formula for checkbook
 

I have a worksheet Icreated to mimick a checkbook. I'm trying to create a
formula for the balance column that would take the previous balance amount in
cell H6 and add the deposit amount, if any, in cell G7and then minus the
check amount in D7 but only if I have an "X" in E7, which means I've mailed
that check.
The following are the columns in my worksheet:
A=Check #, B=Date on Check, C=Transaction Description, D=Payment Amount,
E= Mailed, F=Cleared, G=Deposit Amount, H=Balance

I've been play with the sumif commands and I'm at a lost. Please help.
Thanks!

formula for checkbook
 

Perhaps
=H6+G7-D7*(E7="X")


"ygl" wrote:

I have a worksheet Icreated to mimick a checkbook. I'm trying to create a
formula for the balance column that would take the previous balance amount in
cell H6 and add the deposit amount, if any, in cell G7and then minus the
check amount in D7 but only if I have an "X" in E7, which means I've mailed
that check.
The following are the columns in my worksheet:
A=Check #, B=Date on Check, C=Transaction Description, D=Payment Amount,
E= Mailed, F=Cleared, G=Deposit Amount, H=Balance

I've been play with the sumif commands and I'm at a lost. Please help.
Thanks!

formula for checkbook
 

Hi

I'm not sure where Cleared comes in to your scenario, but ignoring that
for the moment.
Assuming that H2 contains the opening balance on the account.
In H3
=H2+G3-(D3*(E3="x"))


--
Regards

Roger Govier


"ygl" wrote in message
...
I have a worksheet Icreated to mimick a checkbook. I'm trying to
create a
formula for the balance column that would take the previous balance
amount in
cell H6 and add the deposit amount, if any, in cell G7and then minus
the
check amount in D7 but only if I have an "X" in E7, which means I've
mailed
that check.
The following are the columns in my worksheet:
A=Check #, B=Date on Check, C=Transaction Description, D=Payment
Amount,
E= Mailed, F=Cleared, G=Deposit Amount, H=Balance

I've been play with the sumif commands and I'm at a lost. Please
help.
Thanks!

formula for checkbook
 

In order to insert or delete rows the formula should
be modified to use OFFSET Worksheet Function

H7: =OFFSET(H7,-1,0) + G7- D7*(E7="X")

Example of a check balance type worksheet
and reason for use of OFFSET see
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
http://www.mvps.org/dmcritchie/excel/offset.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JMB" wrote in message ...
Perhaps
=H6+G7-D7*(E7="X")


"ygl" wrote:

I have a worksheet Icreated to mimick a checkbook. I'm trying to create a
formula for the balance column that would take the previous balance amount in
cell H6 and add the deposit amount, if any, in cell G7and then minus the
check amount in D7 but only if I have an "X" in E7, which means I've mailed
that check.
The following are the columns in my worksheet:
A=Check #, B=Date on Check, C=Transaction Description, D=Payment Amount,
E= Mailed, F=Cleared, G=Deposit Amount, H=Balance

I've been play with the sumif commands and I'm at a lost. Please help.
Thanks!