Calculations using the last available figure in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jonco - 15 Feb 2007 23:17 GMT

I have a spreadsheet with data in colum A:D 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

Reply to this Message

dkinn - 16 Feb 2007 00:01 GMT

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

> I have a spreadsheet with data in colum A:D rows 5:24
> Lets say they're items in a list. sometimes the list has more itmes than
[quoted text clipped - 26 lines]
>
> Jon

Reply to this Message

Tom Ogilvy - 16 Feb 2007 01:59 GMT

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

Just a thought for the OP.

Signature

Regards,
Tom Ogilvy

> try the following
>
[quoted text clipped - 43 lines]
>>
>> Jon

Reply to this Message

Tom Ogilvy - 16 Feb 2007 00:42 GMT

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))

Signature

Regards,
Tom Ogilvy

>I have a spreadsheet with data in colum A:D rows 5:24
> Lets say they're items in a list. sometimes the list has more itmes than
[quoted text clipped - 26 lines]
>
> Jon

Reply to this Message

JMB - 16 Feb 2007 04:46 GMT

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)

> I have a spreadsheet with data in colum A:D rows 5:24
> Lets say they're items in a list. sometimes the list has more itmes than
[quoted text clipped - 26 lines]
>
> Jon

Reply to this Message