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]

This was the result
Again, Cells I9 and I10 are blank while they should contain the same value as I8 (3) since G9 and G10 have no values.

Oops...

=IF(MATCH(BigNum,$G$6:$G$13)>=ROWS($G$6:G7),
    IF(ISNUMBER(LOOKUP(BigNum,$G$6:G7)),
     SUM(LOOKUP(BigNum,CHOOSE({1,2},0,LOOKUP(BigNum,$I$6:I6))),
       G7,-H7),""),"")

 

[Tornado1981]

Perfect

 

[Aladin Akyurek]

Perfect

Great...

 

[Tornado1981]

Dear Aladin,
I need ur help in solving this problem please
If i add a a value in a fixed cell (H4) and want this value to be added to just the first value that appears in column I (which depend on the corresponding value in column G as u know)

Thank u

 

[Aladin Akyurek]

Dear Aladin,
I need ur help in solving this problem please
If i add a a value in a fixed cell (H4) and want this value to be added to just the first value that appears in column I (which depend on the corresponding value in column G as u know)

Thank u

is this what you mean...

=IF(MATCH(BigNum,$G$6:$G$13)>=ROWS($G$6:G7),
    IF(ISNUMBER(LOOKUP(BigNum,$G$6:G7)),
    SUM((MIN(IF(ISNUMBER($G$7:$G$13),
    ROW($G$7:$G$13)))=ROW())*$H$4,
    LOOKUP(BigNum,CHOOSE({1,2},0,
    LOOKUP(BigNum,$I$6:I6))),G7,-H7),""),"")

which must be confirmed with control+shift+enter?

 

[Tornado1981]

That was perfect
Thank u som much Aladin

 

[Aladin Akyurek]

That was perfect
Thank u som much Aladin

You are welcome.

 

[Tornado1981]

Dear Aladin,
Now i,ve inserted three new columns to my old table (the one to the right)
The three new columns are dependant on just the blue column (Rental balance before)
What i want to do is (if my range is (A3:F12)) :
1) If cell Dx is blank then leave Ex blank
2) If cell Dx have numeric values, then show in cell Ex the result of (Cx+Dx-Bx)
3) If cell Dx is blank and cell E(x-1) (i.e the former cell) contains a value, then show in cell Ex the same value in E(x-1)

So, what would be the formula to use in column E

Can u help me plz ?

 

[Aladin Akyurek]

Dear Aladin,
Now i,ve inserted three new columns to my old table (the one to the right)
The three new columns are dependant on just the blue column (Rental balance before)
What i want to do is (if my range is (A3:F12)) :
1) If cell Dx is blank then leave Ex blank
2) If cell Dx have numeric values, then show in cell Ex the result of (Cx+Dx-Bx)
3) If cell Dx is blank and cell E(x-1) (i.e the former cell) contains a value, then show in cell Ex the same value in E(x-1)

So, what would be the formula to use in column E

Can u help me plz ?

Try...

E3, just enter and copy down:

=IF(D3="",IF(ISNUMBER(LOOKUP(BigNum,$E$2:E2)),E2,""),SUM(C3,D3,-B3))

 

[Tornado1981]

That is what i got .. it's perfect
But is there a way to make E12 blank since A12 is blank too ?
Also the value in cell E10 must be 2 (E9+D10+C10-B10)
Sorry, i didn't mention that before