Capture Minimum and Maximum values from a Single Common dynamic cell.

Hi,

I am looking to Capture Minimum and Maximum values froma single Common Dynamic cell.

Details:

Sheet 1: Pulls dynamic (auto/manual refresh) data from web query.

Sheet 2: Cell A1 displays a dynamic number (profit or Loss and which keeps on changing) which is calculated using dynamic values from Sheet 1. It can be Positive or Negative or can change from Positive to Negative & vice versa during the session/day.

Requirement:

Sheet 2, B1 should show MAX value of Cell A1 reached today (not session).
Eg: In A1, suppose Max Value reached = -10, Min Value reached = -20, B1 cell should display -10 and keep on updating as the value changes.
Eg: In A1, suppose Max Value reached = +50, Min Value reached = -15, B1 cell should display +50 and keep on updating as the value changes.


Sheet 2, C1 should show MIN value of Cell A1 reached today (not session).
Eg: In A1, suppose Max Value reached = -10, Min Value reached = -20, C1 cell should display -20 and keep on updating as the value changes.
Eg: In A1, suppose Max Value reached = +50, Min Value reached = -15, C1 cell should display -15 and keep on updating as the value changes.

Optional:
Sheet 2, D1 should show MAX value of Cell A1 reached till date.
Sheet 2, E1 should show MIN value of Cell A1 reached till date.

Only the value in cell A1 in Sheet2 is to be considered and not the entire column A.

There will be various figures in A column but are only concerned with cell A1.

Tried a few solutions but Minimum is captured only when it is a Negative figure.

If above conditions are too many or complex, a basic solution to capture Minimum / Maximum capture would help too.

Query also posted here: https://chandoo.org/forum/threads/ca...ic-cell.36837/

Thanks.

Hi, Welcome to the forum,

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I agree that a sample file or further explanation would help us help you.

As a guess, your problem sounds similar to these two discussions. A simple circular reference with MIN() and MAX() functions is created (need to enable iteration in Excel options), and the formula will keep track of the largest and smallest value in the changing cell.
https://www.excelforum.com/excel-pro...-possible.html
https://www.excelforum.com/excel-for...ging-cell.html

Rule 08: Cross-posting Without Links

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

Please read what I said again, in particular this bit:

Cross-posting is when you post the same question in other forums on the web.

Once you have complied with the request, you will be able to receive help here.

You will be obliged to provide links on other forums, too - we all have this rule in common, for obvious reasons.

Thank you.

Particularly in the second thread I linked to, I explained that it is important to get the right reset criteria to initialize the loop. Here's what I did:

1) In A3, enter TRUE.
2) In B1, enter =IF($A$3,-1E9,MAX(A1,B1)). I have assumed that you will never see a number smaller than -1E9. You can replace this value with any reasonable smaller-than-you-will-ever-see value.
3) In C1, enter =IF($A$3,1E9,MIN(A1,C1)) Again, replace the 1E9 with a reasonable larger-than-you-will-ever-see value.
4) Because A3 is TRUE, both cells should "reset" to the values you specified. Now, enter FALSE in A3, and these cells should resolve to the current value of A1. As long as A3 is FALSE, these values should track the max and min of the value in A1.
5) Enter the same functions in D1 and E1, with a different "reset" cell (maybe B3).
6a) With this arrangement, you can reset B1 and C1 by entering TRUE into A3 at the beginning of the day, then entering FALSE into A3.
6b) If desired, you can automate this daily reset by including an algorithm in the reset part of the formula that can track your date/time stamps and recognize when date changes. Your sample files don't give any indication how (or even if) your spreadsheets track date, so I haven't explored this possibility.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You've marked [Solved], so I am not sure if you are still looking at this. To automate the daily reset, you could:

1) Enter in B14 =B12<>B13
2) Edit formula in B1 to be =IF(OR($A$3,$B$14),1e9,MAX(A1,B1))
3) Same change to C1

Now, whenever B12 and B13 are not the same, the daily max/min calculations will reset to large/small numbers until B12 and B13 are the same, when the daily tracking can proceed. You still have the option of resetting manually anytime you want by entering TRUE into A3.

Yes, I did mean -1E9 -- good catch. Glad this is working for you.

The file in post #13 still works just fine for me. I can enter values into Sheet1!C37 and the cells in sheet2!A1:D1 still track those changes as expected. I don't think anything changed in Excel that should change the behavior of these functions. If something caused your copy of Excel to disable iteration, that would cause problems, but you should also get a very obvious "circular reference" warning. If calculation was set to manual, that would stop these (and other) functions from updating. Is it just these cells that are no longer updating, or are all formulas no longer updating? Or some other change in your installations options or in the spreadsheet formulas or something.