Calculations using the last available figure in a column
 

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

Calculations using the last available figure in a column
 

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


"jonco" wrote:

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

Calculations using the last available figure in a column
 

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



--
Regards,
Tom Ogilvy


"jonco" wrote in message
. net...
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

Calculations using the last available figure in a column
 

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

Just a thought for the OP.

--
Regards,
Tom Ogilvy



"dkinn" wrote in message
...
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


"jonco" wrote:

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

Calculations using the last available figure in a column
 

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)


"jonco" wrote:

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