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

1. ## 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.

Thanks.

2. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

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.

3. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

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

4. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

@Mr.Shorty

Thanks,
I think gone thru one of the thread a few days ago.

Max() works fine.
Min() works only for Negative numbers.
Iteration enabled.

Will re-check & revert.

@nflsales
Attaching files for reference, an attempt to show my requirements.

5. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

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.

6. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

@AliGW

I had posted only 2 posts on this forum & both are in this thread only.

Thanks.

7. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

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.

8. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

Edited original post.

Thank you.

10. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

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.

11. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

@MrShorty

Looks Great !

Will revert my dumb Q.

Thanks a ton.

13. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

@MrShorty

Manual:
For Today's Max / Min, I will set A3 to TRUE --> FALSE when I first open the file.
For Till Date Max / Min, I will not change D3 and keep it as FALSE.

For Algorithm:
File attached showing date.
Uses =TODAY()

14. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

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.

15. ## Re: Capture Minimum and Maximum values from a Single Common dynamic cell.

2) Did you mean -1e9

Moved Entry Date to another cell.

Using date from web query in place of Entry Date. When the file is opened for first time during the day Daily Max/Min values resets (TRUE). Refreshing data matches Web Date & Today' Date > FALSE and starts capturing daily Max/Min values.

Will not touch Till Date Cells, will keep them FALSE.

Solvedx2

Thanks.

Page 1 of 2 1 2 Last

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1