+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

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

  1. #1
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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.
    Last edited by EEEHHH; 01-04-2018 at 09:37 AM. Reason: Crossposting

  2. #2
    Forum Guru nflsales's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2003; 2007
    Posts
    5,514

    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.
    Click *Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved) If you are satisfied

    Regards

    Siva

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    8,884

    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
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,303

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

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,303

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

    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.

  8. #8
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

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

    Edited original post.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,303

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

    Thank you.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    8,884

    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. #11
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

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

    @MrShorty

    Looks Great !

    Will revert my dumb Q.

    Thanks a ton.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 2016 365 Subscription (Win 10 - Home)
    Posts
    13,303

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

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

  13. #13
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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()
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    8,884

    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. #15
    Registered User
    Join Date
    01-02-2018
    Location
    London
    MS-Off Ver
    2010
    Posts
    7

    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.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

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

Bookmarks

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