+ Reply to Thread
Results 1 to 23 of 23

How to store the lowest value that ever happens in a dynamic cell?

  1. #1
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    How to store the lowest value that ever happens in a dynamic cell?

    Hi all,

    I have an Excel stock portfolio that draws data every 30 seconds from an external API, and refreshes a cell with an updated value of the stock at that point in time.

    I have successfully managed to record the highest value that ever comes into this cell with the Aggregate function [=AGGREGATE(4,6,AI5,M5)], where 4 is MAX, 6 is ignore errors, AI5 is the data cell, and M5 is the cell which stores the highest value.

    However, the Min Function does not work at all. [=AGGREGATE(5,6,AI5,N5).] It just returns a "-"

    Can anyone help me with this please?

    Thank you.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi ExcelNoob09. Welcome to the forum.

    My first thought is that the smallest number is 0 and the cell is formatted to display "-" for those.
    Dave

  3. #3
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi Dave,

    Thank you for the reply, i tried inputting the formula drawing data from a cell with a value already in it, but it still returns a "-"

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to store the lowest value that ever happens in a dynamic cell?

    1. Are you sure you are pulling in a real value, and not text looking like a value?
    2. MIN will always include 0 , if it exists in the reference.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to store the lowest value that ever happens in a dynamic cell?

    My next thought is that
    drawing data from a cell with a value already in it
    the numbers might be text "numbers" which min ignores and would value as 0s.

    Try coercing those with leading "--" like this

    =AGGREGATE(5,6,--AI5,--N5)

    This might confuse AGGREGATE which is expecting a range. The double "--" may cause AGGREGATE to view those as an array which requires a different syntax. I've never tried applying AGGREGATE to 2 non contiguous cells.

  6. #6
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi FDibins,

    Yes the cell is formatted as "currency".
    The Aggregate MAX function works flawlessly and stores the highest value ever keyed into the cell, however the MIN function doesn't

  7. #7
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi Dave,

    Tried =AGGREGATE(5,6,--AI5,--N5), it returned a #VALUE! error.

    To test this out, simply open a fresh excel sheet,

    1. Cell A1 (Key in any number you like)
    2. Cell B1 (Key in =AGGREGATE(4,6,A1,B1) - this will store the Max number that ever comes into Cell A1
    3. Cell C1 (Key in =AGGREGATE(5,6,A1,C1) - this is supposed to store the smallest number that comes in to Cell A1, but it doesn't work.
    4. Randomly key in numbers into Cell A1 to see if the cells store them in the respective formula cells.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Quote Originally Posted by ExcelNoob09 View Post
    Hi FDibins,

    Yes the cell is formatted as "currency".
    Still does not mean it is a real number. If it is a text number (as often happens when pulling values from an external program/app) no amount of formatting will change that.
    Do a quick test with =isvalue(cell-ref) FALSE indicates text

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Edited Incorrect grid post.

    Also the test
    1. Cell A1 (Key in any number you like)
    2. Cell B1 (Key in =AGGREGATE(4,6,A1,B1) - this will store the Max number that ever comes into Cell A1
    returns a circular ref error and then 0.

    This works fine at my end.


    A
    B
    C
    1
    MIN
    MAX
    2
    1
    12
    A2=AGGREGATE(5,6,A3,B3)
    3
    12
    1
    B2=AGGREGATE(4,6,A3,B3)
    Last edited by FlameRetired; 09-23-2018 at 11:14 PM.

  10. #10
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi, it doesn't record the highest values that ever occurred in the cells.
    If you change the values in Row 3, the numbers change accordingly in Row 2

    I need the target cells to store the highest and lowest values from one source cell with dynamically changing values. Hence I only used 3 cells in my example. Cell A1 will dynamically change in value, so you can keep keying in numbers into it.

    The max value will be successfully stored in the Max cell, but the Min cell doesn't .
    Last edited by ExcelNoob09; 09-23-2018 at 11:37 PM.

  11. #11
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12
    Quote Originally Posted by FDibbins View Post
    Still does not mean it is a real number. If it is a text number (as often happens when pulling values from an external program/app) no amount of formatting will change that.
    Do a quick test with =isvalue(cell-ref) FALSE indicates text
    Hello, the values in the cells are indeed numbers , any other suggestions? Appreciate it.

    This is driving me crazy

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi, it doesn't record the highest values that ever occurred in the cells.
    If you change the values in Row 3, the numbers change accordingly in Row 2
    That is the native behavior of Excel formulas.

    Although there are ways that I have seen but never used to do over ride this. Perhaps Ford (FDibbins) remembers.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to store the lowest value that ever happens in a dynamic cell?

    upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

  14. #14
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12
    Quote Originally Posted by FDibbins View Post
    upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    I just left the office for a meeting and will upload the file in a few hours.

    Thank you.

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to store the lowest value that ever happens in a dynamic cell?

    OK thanks...

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to store the lowest value that ever happens in a dynamic cell?

    I am not sure I am following exactly what you are trying to do. However, your description sounds a lot like this problem: https://www.excelforum.com/excel-for...ging-cell.html The challenge with this "circular reference" approach for the minimum is that Excel will start the "loop" with a 0 value, and 0 will always be the minimum. You need some way to "initialize" the loop to some very large positive number so that it can track the minimum from there. A good portion of the linked discussion was about how to initialize/re-initialize the loop so that the circular reference function can correctly track the minimum.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  17. #17
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Hi Mr Shorty,

    This is EXACTLY what i need, but i simply cannot get it to work. It keeps reflecting a "-"

    I have tried the following:
    - Turned on iterations (Max iterations 1, Max Change 0.001)
    - Keying in an absurdly large number in the Target cell to reflect the min value ever recorded in the adjacent dynamic cell bringing in stock quotes
    - Keying in the formula MIN(A1,A2) - A1 Being the dynamic cell, A2 being the cell to store the smallest value
    - Keying in the formula (=IF(A1=0,1000000000,MIN(A1,A2))), - A1 Being the dynamic cell, A2 being the cell to store the smallest value
    - Keying in the formula (=IF(A1<0,1000000000,MIN(A1,A2))), - A1 Being the dynamic cell, A2 being the cell to store the smallest value

    Nothing works

    I have attached my workbook for your scrutiny please. Can't seem to post since im a new member

  18. #18
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    I Stand corrected, the method works if i manually key in the data!

    However, there is another problem. As the Stock values get imported into the dynamic cells, the dynamic data cell will momentarily flash an #N/A while the value is being imported from the server. The #N/A value then gets stuck forever in the MIN cell.

    I used the Aggregate formula to ignore the errors for the MAX cells, how do i do the same for the MIN cells? (Ignore the #N/A error)

    In Summary, MrShorty's method of resetting the loop works if the values are keyed in manually in the dynamic cell, however due to the #N/A value that flashes momentarily in the dynamic cell when it is drawing data from the server, the same #N/A error is locked forever in the target cell that's supposed to store the MIN values ever flashed in the Dynamic stock quotes cell.

    I Tried the formula =IF(ISNUMBER(A1),MIN(A1,A2)) , and it ignores all errors, but always tracks the current value in the dynamic cell, and not the MIN.
    I suspect it's because the values in the MIN cell also refreshes alongside the dynamic cell.

    The Aggregate function for the Max cell specificially bypasses the errors, and the values remain regardless of status of the dynamic source cell. Is there anyway to replicate MrShorty's solution and inbuild it into the Aggregate Function?
    This string specifically [=AGGREGATE(5,6,A1,A2).] where 5 is MIN, 6 is ignore errors, A1 is the dynamic data cell, and A2 is the cell which stores the MIN value that ever comes into A1.

    I hope i am making sense, and thank you so far to all those who have helped
    Last edited by ExcelNoob09; 09-24-2018 at 05:33 AM.

  19. #19
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12
    Anyone has any idea?

  20. #20
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to store the lowest value that ever happens in a dynamic cell?

    I don't understand what is going wrong for you. Why can you not use AGGREGATE() in place of the MIN() function? My version of Excel does not have the AGGREGATE() function, so I cannot test, but it seems like =IF(A2<=0,1E9,AGGREGATE(5,6,A1,A2)) should work, and I cannot say why it does not (though I also cannot be sure if you tried it, either).
    If that really does not work, I could get this to work in my version of Excel =IF(A2<=0,1E9,MIN(IFERROR(A1,A2),A2)) where I use the IFERROR() function to trap the N/A errors that occasionally show up in A1 and ignore them. But it seems like AGGREGATE() should work just fine if you don't need backwards compatibility.

  21. #21
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    Quote Originally Posted by MrShorty View Post
    I don't understand what is going wrong for you. Why can you not use AGGREGATE() in place of the MIN() function? My version of Excel does not have the AGGREGATE() function, so I cannot test, but it seems like =IF(A2<=0,1E9,AGGREGATE(5,6,A1,A2)) should work, and I cannot say why it does not (though I also cannot be sure if you tried it, either).
    If that really does not work, I could get this to work in my version of Excel =IF(A2<=0,1E9,MIN(IFERROR(A1,A2),A2)) where I use the IFERROR() function to trap the N/A errors that occasionally show up in A1 and ignore them. But it seems like AGGREGATE() should work just fine if you don't need backwards compatibility.
    Hi MrShorty,

    Thank you for trying to help me out with this, i tried your following 2 solutions, however it doesnt work, errors explained below:

    Formula 1:
    =IF(A2<=0,1E9,AGGREGATE(5,6,A1,A2))

    Error:
    The Target cell (A2) to store the MIN value always shows the most recent number that comes into the dynamic cell. I notice that the number in the target cell (A2) goes blank when the #N/A error shows in the dynamic cell during the millisecond data draw from the server. The MAX function does not exhibit this behaviour.


    Formula 2:
    =IF(A2<=0,1E9,MIN(IFERROR(A1,A2),A2))

    Error:
    During the data draw phase when the #N/A shows in the dynamic cell (A1), the target cell (A2) also shows the #N/A error and it get stuck in there forever.
    Could there be an error in the formula?

    Apologies for being a noob. Any other advise?

    I hope i can find a solution to this

  22. #22
    Registered User
    Join Date
    09-23-2018
    Location
    France
    MS-Off Ver
    2010
    Posts
    12

    Re: How to store the lowest value that ever happens in a dynamic cell?

    OK Update:

    I Finally got this to work with an inelegant workaround.

    In Short, i used MrShorty's Formula to reset the circular referencing for the Target cell (A2):

    Formula for Target Cell (A2):
    =IF(A2<=0,1E9,AGGREGATE(5,6,A1,A2))


    Followed by an insertion of a formula to IGNORE the #N/A errors in the Dynamic Cell (A1)

    Formula:
    =IFERROR(Original Formula, "")

    This will cause the #N/A error to show as a blank in (A1) when the server draws the info from the API, which is mitigated by MrShorty's previous formula to ignore 0s in the target cell (A2).

    This drove me crazy for days.

    It Works for me and i hope this solution will cut short the pain for others as well.

  23. #23
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: How to store the lowest value that ever happens in a dynamic cell?

    I do not understand why the first formula always shows the most recent number. It seems like it should track the minimum just like the MIN() function. Testing this function in LibreOffice Calc (I cannot test in my older version of Excel), it worked just fine, if I changed the reference from A1,A2 to A1:A2 IF(A2<=0,1e9,AGGREGATE(5,6,A1:A2)). Using my random number generator function in A1 [=IF(RAND()<0.15,NA(),RAND())], this function worked just fine.

    I am not sure why N/A gets stuck in A2 when using formula 2. When I test it in my copy of Excel, it correctly ignores N/A and other errors and just keeps track of the minimum.

    At this point, my guess is that the functions work, but something is happening during the data draw (A1 momentarily contains something that the current logic does not account for) that perhaps needs to be accounted for. Unless we can recreate your data draw, we may not be able to test this on our side.

    Add after seeing previous post: Glad you got it to work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Create a offline html dynamic form and store answer at Excel
    By Judith_Chao in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-02-2018, 02:12 AM
  2. How to store query data into dynamic array?
    By VAer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-30-2018, 10:11 AM
  3. Dynamic Table of Store/Bank with location assignment (Dynamic Inventory)
    By x_ampl1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2017, 01:58 PM
  4. Store/Bank Inventory with Location Dynamic Table
    By x_ampl1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-24-2017, 05:23 AM
  5. [SOLVED] Dynamic Changing Data, Based on Criteria Store in Array, Sum array and store in Cell
    By penbeacho in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-28-2015, 10:31 AM
  6. How to store the value of a dynamic cell
    By bric2007 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2015, 11:57 AM
  7. [SOLVED] How to store the value of a dynamic cell on a fixed cell
    By bric2007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-10-2015, 12:17 AM

Tags for this Thread

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