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

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

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" <(E-Mail Removed)> wrote in message
news:BS5Bh.76560$(E-Mail Removed)...
>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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:557FBDF3-3BD3-4CF3-A354-(E-Mail Removed)...
> 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 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
>>
>>
>>
>>

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