Highest and Lowest Value

[Kingo]

Hi

I've got a sheet linked to some live data that is constantly updating. I'd like to record the highest / lowest value in a single cell without it being written over i.e record the highest value and if there is another value lower it wont overwrite it.

I've tried using the =max or =min but whenever a newer value appears in the cell it just follows that without keeping the higher value?

Thanks

Kingo

[DonkeyOte]

You could use iteration to permit circular references, in Excel Options turn on Iteration with Max Iteration set to 1... if we assume update is in cell A1, MAX to be held in B1, MIN in C1 then:

B1: =MAX(A1,B1)

C1: =MIN(A1,C1)

NOTE: If the value in A1 never drops below 1 you need to alter the approach for C1 as it will initially populate with 0, say:

C1: =MIN(A1,IF(C1,C1,A1))

[Kingo]

Thank you, that seems to work fine but when I close the file down and reopen it I get #N/A in all those columns?

You could use iteration to permit circular references, in Excel Options turn on Iteration with Max Iteration set to 1... if we assume update is in cell A1, MAX to be held in B1, MIN in C1 then:

B1: =MAX(A1,B1)

C1: =MIN(A1,C1)

NOTE: If the value in A1 never drops below 1 you need to alter the approach for C1 as it will initially populate with 0, say:

C1: =MIN(A1,IF(C1,C1,A1))

[martindwilson]

untried on live data but this may work

Please Login or Register  to view this content.

[Kingo]

Sorry Martin, is this a solution for the original question or will this solve the #N/A - if yes how/where do I enter this?

Many thanks

untried on live data but this may work

Please Login or Register  to view this content.

[martindwilson]

er had a problem with that code when i tried to add min to it workbook crashed!!!!

[DonkeyOte]

Kingo, it might be an idea if you could elaborate regards your actual setup... into which cells are the updates occurring ? into which cells do you intend to store MAX/MIN values - ie is each row a separate quote and thus each row has it's own MIN/MAX etc...

[martindwilson]

yep i was about to ask the same thing if its only one cell then its a tad easier!
trouble with min is 0

[Kingo]

Ok the first JPG shows the active price area and the arrows show where the min and max values are being recorded, that seems to work ok.

The 2nd image shows the sheet once's its reopened and that it hasnt updated

Let me know if you need anything further, thanks.

[martindwilson]

oh well not at all how i envisaged it! a workbook is worth a 1000 words! back to donkeyote for the iterations then

[DonkeyOte]

out of curiosity - what happens if you alter the formulae such that:

A27: =MIN(IF(ISNUMBER(E27),E27),IF(A27,A27,IF(ISNUMBER(E27),E27)))
B27: =MAX(IF(ISNUMBER(E27),E27),B27)

Does the same issue persist ?

[Kingo]

Will try later in the week, in meetings till then, thanks.

out of curiosity - what happens if you alter the formulae such that:

A27: =MIN(IF(ISNUMBER(E27),E27),IF(A27,A27,IF(ISNUMBER(E27),E27)))
B27: =MAX(IF(ISNUMBER(E27),E27),B27)

Does the same issue persist ?

[Kingo]

Not sure that formula made the values stick as previously. I've found if I open two copies of the same file - the first one does'nt link through but the second one always does...weird but it seems to do the trick...

out of curiosity - what happens if you alter the formulae such that:

A27: =MIN(IF(ISNUMBER(E27),E27),IF(A27,A27,IF(ISNUMBER(E27),E27)))
B27: =MAX(IF(ISNUMBER(E27),E27),B27)

Does the same issue persist ?