How to save highest value in a continually changing cell

I have a worksheet that includes DDE links that display the latest price of some currencies in column K. Column K is continually changing.

I want to add another column that displays the highest value that column M gets to but whatever I try doesn't work or I get a circular reference error.

How do I do this please? Sample worksheet attached.

Bob

Thanks for the reply MrShorty, much appreciated.

I have set Maximum Iterations to 1 and Maximum Change to .01

I changed cell M3 from
=IF(K3>50,L3*100,L3*10000) to =MAX(M3,IF(K3>50,L3*100,L3*10000))

This works exactly as I wanted but there are 2 problems.

1. When I copy M3 down to the other cells in the column, it shows the M3 value if the other cells are less that M3. If the value in the other cells is greater than M3 the copy is ok. You will see in the pic below that cell M4 is ok but the rest are incorrect.

2. If I add the Max function to a formula in M column that has a negative value it shows the maximum value as zero. I need it to show that -1.123 is greater than -1.135 for example.

3099ca640091f62f2b79feaf77ce5b66.png

I did briefly read that but didn't really understand it. I will have another read.

I certainly don't fully understand reset criteria but I have changed M3 to =IF(M3=0,-5000,MAX(M3,IF(K3>50,L3*100,L3*10000)))

If I copy that to the rest of of the rows I get the same result as previous.

I should mention that the only time I need to reset is when I first add a new row. The processing in that row will continue for a day up to a few months. When I see the result I am looking for (good or bad) I will clear the DDE link for that row.

Yes that seems to do the trick and I do need a reset for each row. I'll set it all up and try it for a day or so and let you know the result.

Thanks for your help MrShorty, much appreciated.

It's doing exactly what I wanted. It ran overnight and all results look exactly as expected.

Cheers
Bob