Forum Discussion
Return the value of previous non empty cell in the same column
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
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.
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)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.
