Return the value of previous non empty cell in the same column

Highlighted
Occasional Visitor

Hello,

 

I need to find the difference between 2 cells in a column from my data

 

Attached excel is a sample of how I want the result to be (which I did manually), But my data is vast so I want a dynamic formula.

 

Lets say I'm looking to calculate X-Y, I want excel to pick the X value from a cell (few columns away) in the same row in which I want the result, and Y should be the last non empty cell in the same column above X

 

Thanks in advance,

Yogesh J

3 Replies
Highlighted

@jastiyogesh 

That could be

=IF($B2>0,$B2-INDEX($B:$B,AGGREGATE(14,6,1/(LEN($B$1:INDEX(B:B,ROW()-1))>0)*ROW(B:B),1)),"")

and drag it down, please see in attached.

Highlighted

@jastiyogesh 

In the attached file, the formula in C2, copied down rows, is: 

=IFERROR(IF(B2="","",
B2-INDEX(B:B,
LOOKUP(2,1/(B$1:B1<>""),ROW(B$1:B1)))),B2)

Highlighted

@jastiyogesh 

I realized I must have been blinded by the Einstellung Effect! My earlier formula should have been shortened to this: 

=IFERROR(IF(B2="","",
B2-LOOKUP(2,1/(B$1:B1<>""),
B$1:B1)),B2)

See the foregoing formula in C2 of the attached file.