A running-balance column in a bank-tracking table

[Guest]

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:

Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

Each row/entry will be a single transaction. It may have the same date as
another entry, because there might be several deposits, credits, bank fees on
a given day. So for example, there might be 3 entries on 7/25/07.

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to do
this. Any relatively easy way to do this, or otherwise achieve my goal of a
running balance that auto-corrects as I add/delete entries over time?

 

[Sandy Mann]

thinking that I could make H have a formula that would say "Subtract the

sum-total of C-D-E from the previous cell".

With the headers in Row 1 enter in H2:

=IF(COUNTA(B2:G2)>0,C2-SUM(D2:E2),"")

In H3 enter:

=IF(COUNTA(B3:G3)>0,H2+C3-D3-E3,"")

and copy down.

I am assuming that it may be that there is data in F or G but no entry in C,
D or E
If that is not he case then you can use COUNT() in place of COUNTA() as in

=IF(COUNTA(B4:G4)>0,H3+C4-D4-E4,"")

although COUNTA() is doing no harm.

An alternative would be:

=IF(COUNT(C2:E2)>0,SUM($C$2:C2)-SUM($D$2:E2),"")
in H2 and copy down.

If you make an error in entering say the Bank fees by typing 10,00 when your
decimal symbol is a period, the first set if formulas with give you #VALUE!
whereas the SUM() one will present an blank looking cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:

Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

Each row/entry will be a single transaction. It may have the same date as
another entry, because there might be several deposits, credits, bank fees
on
a given day. So for example, there might be 3 entries on 7/25/07.

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to
do
this. Any relatively easy way to do this, or otherwise achieve my goal of
a
running balance that auto-corrects as I add/delete entries over time?

 

[Sandy Mann]

If that is not he case then you can use COUNT() in place of COUNTA() as in

=IF(COUNTA(B4:G4)>0,H3+C4-D4-E4,"")

So why did you use COUNTA() again Sandy?

Because I copied the wrong formula from the sheet! should be:

=IF(COUNT(B2:E2)>0,C2-SUM(D2:E2),"")

Doh!

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell".

With the headers in Row 1 enter in H2:

=IF(COUNTA(B2:G2)>0,C2-SUM(D2:E2),"")

In H3 enter:

=IF(COUNTA(B3:G3)>0,H2+C3-D3-E3,"")

and copy down.

I am assuming that it may be that there is data in F or G but no entry in
C, D or E
If that is not he case then you can use COUNT() in place of COUNTA() as in

=IF(COUNTA(B4:G4)>0,H3+C4-D4-E4,"")

although COUNTA() is doing no harm.

An alternative would be:

=IF(COUNT(C2:E2)>0,SUM($C$2:C2)-SUM($D$2:E2),"")
in H2 and copy down.

If you make an error in entering say the Bank fees by typing 10,00 when
your decimal symbol is a period, the first set if formulas with give you
#VALUE! whereas the SUM() one will present an blank looking cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:

Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

Each row/entry will be a single transaction. It may have the same date as
another entry, because there might be several deposits, credits, bank
fees on
a given day. So for example, there might be 3 entries on 7/25/07.

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to
do
this. Any relatively easy way to do this, or otherwise achieve my goal of
a
running balance that auto-corrects as I add/delete entries over time?

 

[Stan Brown]

Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:
Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to do
this. Any relatively easy way to do this, or otherwise achieve my goal of a
running balance that auto-corrects as I add/delete entries over time?

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

To me it seems pretty straightforward to put the starting balance in
H2 and this in H3:
=H2 + C2 + D2 - E2
and then click and drag that formula down column H.

Incidentally, did you mean column C to be "credits" in the accounting
sense, meaning that they would reduce the total of cash on hand? In
that case the formula would be
=H2 - C2 + D2 - E2

 

[Sandy Mann]

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

The purpose of the COUNT() function is to check if there are any entries in
that row and if not, return an empty string. Just makes it look better that
a whole column of zeros when there are no entries in those rows.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:
Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to
do
this. Any relatively easy way to do this, or otherwise achieve my goal of
a
running balance that auto-corrects as I add/delete entries over time?

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

To me it seems pretty straightforward to put the starting balance in
H2 and this in H3:
=H2 + C2 + D2 - E2
and then click and drag that formula down column H.

Incidentally, did you mean column C to be "credits" in the accounting
sense, meaning that they would reduce the total of cash on hand? In
that case the formula would be
=H2 - C2 + D2 - E2

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

 

[Guest]

Thanks for the info; this will solve my problem. I'll adjust the formula to
fit those numbers that need to be added and those that need to be subtracted.

ddc

Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:
Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to do
this. Any relatively easy way to do this, or otherwise achieve my goal of a
running balance that auto-corrects as I add/delete entries over time?

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

To me it seems pretty straightforward to put the starting balance in
H2 and this in H3:
=H2 + C2 + D2 - E2
and then click and drag that formula down column H.

Incidentally, did you mean column C to be "credits" in the accounting
sense, meaning that they would reduce the total of cash on hand? In
that case the formula would be
=H2 - C2 + D2 - E2

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

 

[Guest]

Thank you Sandy. I think Stan's solution will be easier for me and I'll have
to live with the less-appealing visual appearance of the table till I'm a
little more advanced with the formula-making. But I'll copy this down in my
Excel notes for use then.

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

The purpose of the COUNT() function is to check if there are any entries in
that row and if not, return an empty string. Just makes it look better that
a whole column of zeros when there are no entries in those rows.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee

I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:
Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

But for each entry, only 1 of C-D-E will have a number-value for a given
entry. I'd like to have column H look at c, d, and e and add or subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract the
sum-total of C-D-E from the previous cell". But I can't figure out how to
do
this. Any relatively easy way to do this, or otherwise achieve my goal of
a
running balance that auto-corrects as I add/delete entries over time?

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

To me it seems pretty straightforward to put the starting balance in
H2 and this in H3:
=H2 + C2 + D2 - E2
and then click and drag that formula down column H.

Incidentally, did you mean column C to be "credits" in the accounting
sense, meaning that they would reduce the total of cash on hand? In
that case the formula would be
=H2 - C2 + D2 - E2

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

 

[Sandy Mann]

You're welcome. It will save you asking later how you can hide all the
zeros <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Thank you Sandy. I think Stan's solution will be easier for me and I'll
have
to live with the less-appealing visual appearance of the table till I'm a
little more advanced with the formula-making. But I'll copy this down in
my
Excel notes for use then.

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

The purpose of the COUNT() function is to check if there are any entries
in
that row and if not, return an empty string. Just makes it look better
that
a whole column of zeros when there are no entries in those rows.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Mon, 3 Sep 2007 01:46:01 -0700 from DeeDeeCee
<[email protected]>:
I'm making a table to keep track of deposits, credits, etc for a bank
account, with a running balance. Here is the basic layout:
Column b = date of entry
Column c = credits
Column d = deposits
Column e = bank fees
[ Column F & G = other info, not relevant here ]
Column H = Running balance

But for each entry, only 1 of C-D-E will have a number-value for a
given
entry. I'd like to have column H look at c, d, and e and add or
subtract
whichever one has info in it from the previous cell in Column H. I was
thinking that I could make H have a formula that would say "Subtract
the
sum-total of C-D-E from the previous cell". But I can't figure out how
to
do
this. Any relatively easy way to do this, or otherwise achieve my goal
of
a
running balance that auto-corrects as I add/delete entries over time?

I don't understand Sandy's solution because I can't see any need for
a COUNT function.

To me it seems pretty straightforward to put the starting balance in
H2 and this in H3:
=H2 + C2 + D2 - E2
and then click and drag that formula down column H.

Incidentally, did you mean column C to be "credits" in the accounting
sense, meaning that they would reduce the total of cash on hand? In
that case the formula would be
=H2 - C2 + D2 - E2

--
"First prove what you're saying, then whine about it."
-- /The People's Court/
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/