Calculations using the last available figure in a column

[jonco]

I have a spreadsheet with data in colum A rows 5:24
Lets say they're items in a list. sometimes the list has more itmes than
others.
Description Cash IN Cash Out Running Balance

If there is nothing in either the Cash In or Cash Out cells then therre will
be no running balance. for thaqt line. The last balance figure is the
running balance.

In row 25 I have different data that is sometimes there buy not always (But
it's always in row 25). Let's call that Overhead

I want to be able to get the earlier running balance figure (from somwherw
above) and Add the Cash In Figure (B25) or subtract the Cash Out figure
(C25) (if any) and update the Running Balance figure accordingly.

I use this formula to post the running balance figure if there is a plus or
minus:
=IF((B7="")*AND(C7=""),"",D6+B7-C7) -- (Example shows row 7) It displays
as blank if there is no changes to the balance figure above.

If I copy the same formula to row 25...I get a #VALUE! error when I put data
in row 25.

So, how do I get the last available figure in the column and do a
calculation on it in row 25?

Thanks for any suggestions.

Jon

 

[Guest]

try the following

=IF((B7="")*AND(C7=""),"",SUM(D6,B7,-C7))

the sum function will ignor the text that was giving you the problem and
still allow you to add and subtract

at least it seems to in my sample

David

 

[Tom Ogilvy]

Assume D1 has the beginning balance and entries start in row 2.

Enter In
D2:=if(And($B2="",$C2=""),"",$D$1+Sum($B$2:$B2)-Sum($C$2:$C2))

Then fill down.

this would result in:

D7: =if(And($B7="",$C7=""),"",$D$1+Sum($B$2:$B7)-Sum($C$2:$C7))

 

[Tom Ogilvy]

True, but how does it accumulate the total if the previous total (D6 for
example) is ""

Just a thought for the OP.

 

[Guest]

Another suggestion (but not quite as short as Tom's) - you could use this in
your formula in D2 and copy down. It should pull out the last number.

Lookup(max($D$1:$d1)+1,$d$1:$d1)