Need help with an Excel formula 1

[cal555]

Hi, I made a simple checkbook program, that through a formula will add or subtract to the balance depending if a debit or credit.

Check Book Program

D E F
Date debit Credit Balance
0 10 124.00
4 120.00
120.00
120.00
120.00

That formula I am using is:

=(IF(D9=0,F8+E9,F8-D9))

and it works great. However, in the balance column it repeatas the balance all the way down, because I have the fomula all the way down; so that it will compute the balance. What I want is to add to this formula so that if it so that if D and E are blank it will make Coulmn F blank, until it has something to compute.

 

[xlhelp]

=IF(D9="",IF(E9="","",F8-D9+E9))

Member AAA - Abolish Abused Abbreviations

 

[cal555]

Thanks for your help, but this gave me an an error. And there would not be a time subtract from my balance my credit and add to it my debit to it; as your formula is showing. What I need is to keep the formula as I have it above, that is working. Then add to it, if both columns D and E are blank; I want column F to be blank, other wise I want it to add the or subtract the debit and show the balance.
Thank You

 

[xlhelp]

If F9 is your new balance, D9 is your withdrawal and E9 is your deposit, then F8-D9+E9 should work, since if you have a deposit, then your debit is zero; and as such old bal plus deposit minus zero would produce the result you desire. Similary if you had a withdrawal, then old bal plus zero - minus withdrawal would give the new balance.

Are you D and E columns switched?

Member AAA - Abolish Abused Abbreviations

 

[cheerio]

You could leave your formula in the original form but use conditional formatting to change the font colour to white if columns D and E are blank.

Almost equivalent and easier to set up would be to change the font colour to white if the balance value is unchanged from the row above.

 

[SkipVought]

cal,

[tt]
=IF(AND(D9="",E9=""),"",IF(E9="","",F8-D9+E9))
[/tt]

Skip,

_[red][/red]

 

[cal555]

I see the logic in what you are doing, and it seemd to me you would be right but when I use either formula:

=IF(D9="",IF(E9="","",F8-D9+E9))

OR

=IF(AND(D9="",E9=""),"",IF(E9="","",F8-D9+E9))
and put a debit of 2 dolars in I get thsi:

D E F
70
2.00 FALSE
FALSE
919.25

Acttaily it does not matter irf it is 70 or 500 I get False False and 919.25
I am not sure what is happening here.
And I can not see how to use connditional formating be cause it only looks at one column and I need to look at two columns.

 

[xlhelp]

Ahhh, I am always too quick, that's my problem
My formula is wrong.

Try Skip's with a mod:

=IF(AND(D9="",E9=""),"",F8-D9+E9)

Member AAA - Abolish Abused Abbreviations

 

[xlhelp]

Ok, mine should have been:

=IF(D9="",IF(E9="","",F8-D9+E9),F8-D9+E9)

and shorter one would be:
=IF(E9-D9=0,"",F8-D9+E9)

Member AAA - Abolish Abused Abbreviations

 

[cheerio]

And I can not see how to use conditional formating be cause it only looks at one column and I need to look at two columns.

Not true.

Change the dropdown box from "cell value is" to "formula is". You now need a formula tht returns TRUE or FALSE.

=AND(ISBLANK(E2),ISBLANK(F2))

will test for blanks in E2 and F2 and can be used to apply a format in G2.

Similarly
=(C2=C1)

can be used on C2 to force it to white font if it is unchanged from C1

 

[cal555]

Thanks for everones help, but for some reason I am not able to get the formulas to work. I get different values then I should have, depending on what I use, and none cause column F to go blank if both E & D are blank.
=IF(D9="",IF(E9="","",F8-D9+E9),F8-D9+E9)

and shorter one would be:
=IF(E9-D9=0,"",F8-D9+E9)

But I did get the conditional formating to work.
Thank you