Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-783380%22%20slang%3D%22en-US%22%3EReturn%20the%20value%20of%20previous%20non%20empty%20cell%20in%20the%20same%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783380%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20find%20the%20difference%20between%202%20cells%20in%20a%20column%20from%20my%20data%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20excel%20is%20a%20sample%20of%20how%20I%20want%20the%20result%20to%20be%20(which%20I%20did%20manually)%2C%20But%20my%20data%20is%20vast%20so%20I%20want%20a%20dynamic%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELets%20say%20I'm%20looking%20to%20calculate%20X-Y%2C%20I%20want%20excel%20to%20pick%20the%20X%20value%20from%20a%20cell%20(few%20columns%20away)%20in%20the%20same%20row%20in%20which%20I%20want%20the%20result%2C%20and%20Y%20should%20be%20the%20last%20non%20empty%20cell%20in%20the%20same%20column%20above%20X%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3EYogesh%20J%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-783380%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783470%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20the%20value%20of%20previous%20non%20empty%20cell%20in%20the%20same%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385543%22%20target%3D%22_blank%22%3E%40jastiyogesh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-ruby%22%3E%3CCODE%3E%3DIF(%24B2%26gt%3B0%2C%24B2-INDEX(%24B%3A%24B%2CAGGREGATE(14%2C6%2C1%2F(LEN(%24B%241%3AINDEX(B%3AB%2CROW()-1))%26gt%3B0)*ROW(B%3AB)%2C1))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%2C%20please%20see%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783510%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20the%20value%20of%20previous%20non%20empty%20cell%20in%20the%20same%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783510%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385543%22%20target%3D%22_blank%22%3E%40jastiyogesh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20C2%2C%20copied%20down%20rows%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(IF(B2%3D%22%22%2C%22%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EB2-INDEX(B%3AB%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3ELOOKUP(2%2C1%2F(B%241%3AB1%26lt%3B%26gt%3B%22%22)%2CROW(B%241%3AB1))))%2CB2)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-785078%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20the%20value%20of%20previous%20non%20empty%20cell%20in%20the%20same%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-785078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385543%22%20target%3D%22_blank%22%3E%40jastiyogesh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realized%20I%20must%20have%20been%20blinded%20by%20the%20%3CSTRONG%3EEinstellung%20Effect%3C%2FSTRONG%3E!%20My%20earlier%20formula%20should%20have%20been%20shortened%20to%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(IF(B2%3D%22%22%2C%22%22%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EB2-LOOKUP(2%2C1%2F(B%241%3AB1%26lt%3B%26gt%3B%22%22)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EB%241%3AB1))%2CB2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESee%20the%20foregoing%20formula%20in%20C2%20of%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
jastiyogesh
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

@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.

@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)

@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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies