Retrieve last non blank value in a list

[Tornado1981]

Hi,
I would really appreciate if u help me on solving this
I want a formula in each cell in column C to do this :
(Taking C5 as an example)
Test if there is a value in A5 .. if not found, then leave C5 blank .. else, test if there is a value in ($C$1:C4) .. and if found, then add the last value found in ($C$1:C4) to A5 and subtract B5 from the result .. else [ no values found in ($C$1:C4) ], then subtract B5 from A5

Thank u so much

Note : I used the offset function to retrieve the last non blank value in ($C$1:C8)

 

[wigi]

In C2:

=if(A2="","",SUM($A$1:$A2)-SUM($B$1:$B2))

Copy down.

 

[Tornado1981]

Thank u for ur help wigi
But when i applied ur formula, the cells C3,C4,C7&C8 got blank , while i want them to contain the same former value if the corresponding cell in column A is blank (as shown in image).

 

[wigi]

You wrote earlier:

Test if there is a value in A5 .. if not found, then leave C5 blank

 

[Tornado1981]

Sorry if i confused u
What i wanted to say is that if A5 is blank and (C1:C4) is blank too .. then leave C5 blank .. else if A5 is blank but (C1:C4) contains a numeric value, then add the last value found in (C1:C4) to A5 then subtract B5 from the result.

Which will make the result typically as shown in image

Thanks and sorry for disturbing u

 

[wigi]

Like this?

=if(SUM($A$1:$A2)=SUM($B$1:$B2),"",SUM($A$1:$A2)-SUM($B$1:$B2))

 

[Tornado1981]

Yaa .. that's it wigi
Thank u sooooo much

 

[Aladin Akyurek]

Hi,
I would really appreciate if u help me on solving this
I want a formula in each cell in column C to do this :
(Taking C5 as an example)
Test if there is a value in A5 .. if not found, then leave C5 blank .. else, test if there is a value in ($C$1:C4) .. and if found, then add the last value found in ($C$1:C4) to A5 and subtract B5 from the result .. else [ no values found in ($C$1:C4) ], then subtract B5 from A5

Thank u so much

Note : I used the offset function to retrieve the last non blank value in ($C$1:C8)

Try...

C2, just enter and copy down:

=IF(ISNUMBER(A2),SUM(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,$C$1:C1)),A2,-B2),"")

Note. If so desired, you can use the truncated version 9.99E+307 for Excel's big number constant.

 

[T. Valko]

Try...

C2, just enter and copy down:

=IF(ISNUMBER(A2),SUM(LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,$C$1:C1)),A2,-B2),"")

Note. If so desired, you can use the truncated version 9.99E+307 for Excel's big number constant.

Or, you could use the much shorter 1E100 and get the EXACT same result!

=IF(COUNT(A2),SUM(LOOKUP(1E100,CHOOSE({1,2},0,$C$1:C1)),A2,-B2),"")

 

[Tornado1981]

Thank u aladin

But when i applied ur formula i got a #value! error in cells C5 and C6