Subtract last value in the row.

[bdtran]

Hello All,

I am trying to get an excel formula to get result in row#2 based on the value data in row#1.

The first value in row#1 is always equal to same value which is 5, following the next number, which is 16, the result must be 16-5=11, and scanning through the next value is 20 subtract the last lookup value on same row is 16, which should be 20-16=4. I hope I am getting my point clear.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]16[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks much,
*BDT

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try
B2 copied across
=IFERROR(IF(B1<>"",B1-LOOKUP(2,1/($A1:A1<>""),$A1:A1),""),B1)

Hope this helps

M.

 

[Scott Huish]

Also:
B2 copied across:
=IFERROR(IF(B1<>"",B1-LOOKUP(9.99999999E+307,$A1:A1),""),B1)

 

[bdtran]

Thank you to Marcelo and Scott.

I tried both solutions and got the same result that I am looking for.

Much appreciated!

*BDT