How to get cells value from previous visible row in excel formulas

Question

Is it possible to write a formulas in Excel,to calculate value based on the previous visible row?

By applying a Filter on the columns, the previous visible row changes but the usual formals does not consider visibility of the previous row, so the result does not change by applying filters. For example:

Let's original values of the spreadsheet cells be:

   A | B | C | D 
1: 5   3   1
2: 9   1   1
3: 2   3   0  
4: 7   8   1   =A3-B4 equals 2-8=-6

Now assume that we make a filter on C column to hide the third row so we have

   A | B | C | D 
1: 5   3   1
2: 9   1   1
4: 7   8   1   =A3-B4 is still equals -6 but I want to get: 9-8=1

Is it possible to get such a formulas? Thank you very much.

Answer 1

Try this formula

=LOOKUP(2,1/SUBTOTAL(3,OFFSET(A$1,ROW(A$1:A3)-ROW(A$1),0)),A$1:A3)-B4

The SUBTOTAL/OFFSET part returns a 1 or zero for each value in column A depending on whether it's visible or not, LOOKUP finds the last 1 (equivalent to the last visible value) and gives that value.