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

Originally Posted by MrShorty

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

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.