 • 441K Members
• 4,996 Online
• 529K Conversations

# 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

Yogesh J

3 Replies

# Re: Return the value of previous non empty cell in the same column

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.

# Re: Return the value of previous non empty cell in the same column

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)

# Re: Return the value of previous non empty cell in the same column

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies