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)

 

[Aladin Akyurek]

Thank u aladin

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

You are right... You have already a good proposal from wigi.
The following is just for the record:

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

where BigNum is a defined name that stands for 9.99999999999999E+307.

 

[Tornado1981]

Thank u so much Aladin for concern

when i applied ur formula, Cells C3,C4,C7 & C8 got blank while i want them to contain the value in the former cells.

so is there a way to solve this ?

and also is there a way a combined offset-max-row function to solve this ?

 

[wigi]

when i applied ur formula, Cells C3,C4,C7 & C8 got blank while i want them to contain the value in the former cells.

so is there a way to solve this ?

As already said by Aladin, try my proposal above.

 

[Tornado1981]

Yes wigi, ur solution was perfect
I was just asking to learn more

Anyway, i got another problem

If the cell A6 is the lat cell containing numeric value in column A , then i want cells C6:C8 to be blank

any suggestions ?

 

[Aladin Akyurek]

Thank u so much Aladin for concern

when i applied ur formula, Cells C3,C4,C7 & C8 got blank while i want them to contain the value in the former cells.

so is there a way to solve this ?

and also is there a way a combined offset-max-row function to solve this ?

Yes wigi, ur solution was perfect
I was just asking to learn more

Anyway, i got another problem

If the cell A6 is the lat cell containing numeric value in column A , then i want cells C6:C8 to be blank

any suggestions ?

Taking up both points...

C2, just enter and copy down:

=IF(MATCH(BigNum,$A$1:$A$8)>=SUM(ROW(A2)),
    SUM(LOOKUP(BigNum,CHOOSE({1,2},0,LOOKUP(BigNum,$C$1:C1))),A2,-B2),"")

Recall that BigNum's definition refers to =9.99999999999999E+307.

 

[Tornado1981]

That was perfect Aladin
Thank u soooooooooooooo much

thank u so much too wigi

 

[Aladin Akyurek]

That was perfect Aladin
Thank u soooooooooooooo much

thank u so much too wigi

You are welcome. Thanks for providing us feedback.

 

[T. Valko]

Taking up both points...

C2, just enter and copy down:

=IF(MATCH(BigNum,$A$1:$A$8)>=SUM(ROW(A2)),
SUM(LOOKUP(BigNum,CHOOSE({1,2},0,LOOKUP(BigNum,$C$1:C1))),A2,-B2),"")


Recall that BigNum's definition refers to =9.99999999999999E+307.

You don't need that first SUM(...) function.

Also, you might want to use ROWS(A$2:A2)+1 rather than ROW(A2).

Using ROW(A2) leaves the formula vulnerable to new row insertions before row 3. If you know for certain that you will never be inserting new rows before row 3 then it'll be ok.

1E100 is still shorter than BigNum and doesn't need to be defined!

 

[Tornado1981]

Dear Aladin

Sorry for disturbance again

I got a problem when i apply ur formula in a table range other than A:C
(for example G:H)

All the cells in column H got blank

So how to solve this ?

 

[Aladin Akyurek]

Dear Aladin

Sorry for disturbance again

I got a problem when i apply ur formula in a table range other than A:C
(for example G:H)

All the cells in column H got blank

So how to solve this ?

Do you mean F:H or G:I for we need three columns?

If the latter...

=IF(MATCH(BigNum,$F$1:$F$8)>=SUM(ROW(F2)),
    SUM(LOOKUP(BigNum,CHOOSE({1,2},0,LOOKUP(BigNum,$I$1:I1))),G2,-H2),"")