How to substract previous row on a filtered Excel table

Question

I need to a formula that calculates the difference between the current row and the imediatly previous visible row. The formula is not working when table is filtered. It gives the difference the current row and the previous row by number irrespective of the Filter.

Formula on cell D2 [diff]

=IF(ROW()=2;[@[value]];[@[value]]-OFFSET([@[value]],-1,0))

    date    ID  value   diff
03/08/2020  1   120,00  120
03/08/2020  2   32,00   -88
03/08/2020  3   32,00   0
03/08/2020  3   12,00   -20
03/08/2020  4   13,00   1
03/08/2020  4   132,00  119
03/08/2020  4   112,00  -20
03/08/2020  4   323,00  211

Answer 1

OFFSET doesn't do that. You will need to modify the formula to detect the previous visible row.

I'd do that by adding a helper column IsVisible (which you can hide if you want) to the table that returns the row numder if visible, else 0. Formula to do that is

=AGGREGATE(3,5,[@date])*ROW()

Then, the Diff formula looks up the Value from the row where VisibleRow is Max of VisibleRow's < current row

=XLOOKUP(MAXIFS([IsVisible],[IsVisible],"<"&[@IsVisible]),[IsVisible],[Value],0,0)

Put that into your formula, which becomes

=IF(ROW()=ROW([#Headers])+1,[@Value],[@Value]-XLOOKUP(MAXIFS([IsVisible],[IsVisible],"<"&[@IsVisible]),[IsVisible],[Value],0,0))

_{Note 1, I've replaced the hard coded row 2 with a formula to remove the requirement the table starts at a specific row}

_{Note2: the depends on the new functions XLOOKUP and MAXIFS, available in Excel 365. It can be modified to use older formula if required}

Unfiltered

Filtered