Keeping track of highest values in Calc

[PhilE]

I am trying to track the highest value over a period of time. I tried using the IF function ie.
Cell D3 contains:- =IF((A3*B3)>F3,(A3*B3),D3 and E3 contains:- =IF(A3*B3>F3,C3,E3) where A3 contains the number of units, B3 the daily price,C3 the current date,D3 the highest value, E3 The date of the highest value and F3 the current Value. It appears that a cell is undated as soon as it is referred to, so the value in F3 is updated before the IF function in D3 & E3 are evaluated. Is there a way around this to achieve my goal?

Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-IN (en_US); UI: en-GB
Calc: threaded

[gregors15]

Have a look at the attached sheet, I have something similar,
on the 1st line the columns are as follows,

Cols A-H my data.
Col I shows the max price.
Col J latest price.
Col K %Change from Max to latest.
Col L shows the max price.
Col M Number of days max price.
Col N Latest date max price achieved.
Col O Row number where max price was achieved(just for checking).

Column L lines is populated with Col A date, if the current value(Col E) is equal to the max price, (the max price could be achieved on more than I day), =IF(E4=$L$1,A4,0).

Let us know if this helps. Produced in Windows 10 Home, LO 7.5.0.3
LOQ_20230224.ods (88.7 KB)

[PhilE]

Thank you for your prompt reply, after a quick look at your spread sheet there is some food for thought. As I have a number of items to cover, I was trying to use 1 line per item and update each line on a daily basis rather than a line for each day. This would mean a large spread sheet or sheets to accomplish.
All I am trying to do is to capture the highest value and the date on which it occurs but unfortunately the formula I have come up with does not work as the daily value is updated before the IF function is evaluated, so the result is always “false” and the higher value and date is never greater than the current value.