Storing a value on a cell dynamically

[vladi305]

I'm trying to count from bottom to top the distance between 1 values. The 1 value represent an event on a particular date
the formula below when the event is found it extract the numeric value of the cell address and the difference of the previous value with the next one is the distance
so in cell A1 I store manually the value from the previous value of what I will use as the starting point. The idea will be to drag up the cursor and get the count
The question is how to set the value of the previous cell in A1 dynamically
As you notice the result on F17 is correct, the distance from F27 to F17 is 10 but from F17 to F3 is 14 NOT 24

To get the correct value the cell A1 would need to be updated when the formula reach the F3 cell

=LET(
lastVar, $A$1,
nextVar, TEXTJOIN("",TRUE,IFERROR((MID(CELL("address",INDEX($F17,MATCH(1,$E17,1))),ROW(INDIRECT("1:"&LEN(CELL("address",INDEX($F17,MATCH(1,$E17,1)))))),1)*1),"")),
IF(E17=1, lastVar - nextVar, ""))

 

[Fluff]

Duplicate to: How to calculate distance between values

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.