Formula Problem For Checkbook Register
 

Here is what I am trying to do
Cell Name Account Formula Used
A2 debit Checking
B2 credit Checking
C2 total Checking total
=IF(AND(ISBLANK(A2),ISBLANK(B2)),"",C1-A2+B2)
D2 debit Savings
E2 credit Savings
F2 total Savings Total
=IF(AND(ISBLANK(D2),ISBLANK(E2)),"",F1-D2+E2)
G2 Collected Total

My problem is getting a total for G2. If nothing is in the debit or credit
spaces, excel will not add a number to it (which i like) but i need a formula
that basically says if C2 has a number but F2 does not have a number i want
it to add the total of the two cells anyways and put the total in G2

As it stands now I have to add a "0" to credit or debit just to get a total
of the two cells, and i don't want that because it makes it harder to see
what the total is to the savings account + it keeps adding the new amount
each time it has a total in F2.

If anyone can help me, it would be greatly appreciated.
Thanks in advance

Formula Problem For Checkbook Register
 

I assume you're copying all of these formulas down the columns and that each
row represents a tranaction for a particular date.

If so, then you'll have a problem if you today you had a debit in the
checking but nothing in the savings and then tomorrow you had a credit in
the savings but nothing in the checking. The problem will eventually arise
from trying to calculate cells that contain the formula blank "".

So, assuming cells C1 and F1 are your starting balances for each account
type

Enter this formula in C2:

=IF(COUNT(C$1),IF(COUNT(A2:B2),LOOKUP(1E100,C$1:C1 )-A2+B2,""),"")

Enter this formula in F2:

=IF(COUNT(F$1),IF(COUNT(D2:E2),LOOKUP(1E100,F$1:F1 )-D2+E2,""),"")

Enter this formula in G2:

=IF(COUNT(C2,F2),SUM(C2,F2),"")

Copy all formulas down as needed.


--
Biff
Microsoft Excel MVP


"Kentram" wrote in message
...
Here is what I am trying to do
Cell Name Account Formula Used
A2 debit Checking
B2 credit Checking
C2 total Checking total
=IF(AND(ISBLANK(A2),ISBLANK(B2)),"",C1-A2+B2)
D2 debit Savings
E2 credit Savings
F2 total Savings Total
=IF(AND(ISBLANK(D2),ISBLANK(E2)),"",F1-D2+E2)
G2 Collected Total

My problem is getting a total for G2. If nothing is in the debit or
credit
spaces, excel will not add a number to it (which i like) but i need a
formula
that basically says if C2 has a number but F2 does not have a number i
want
it to add the total of the two cells anyways and put the total in G2

As it stands now I have to add a "0" to credit or debit just to get a
total
of the two cells, and i don't want that because it makes it harder to see
what the total is to the savings account + it keeps adding the new amount
each time it has a total in F2.

If anyone can help me, it would be greatly appreciated.
Thanks in advance