help with formula

[Treesy]

Hi there. I have my checkbook register in Excel and I'd like to add a column
that shows "actual balance", based on items that have cleared my account.
Currently, column G has the following formula to calcuate the balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can the
above formula be altered so that if there is a C in column D, then it goes
ahead and performs the calculation. If there is no C, it does no calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected balance
(assuming everything clears my account) and one would be an actual balance
(to compare against the balance showing on my checking account). If anyone
can think of a different way to accomplish this, I'm open to ideas.

Thanks for your help!!

 

[Elkar]

For your "Projected" amount (column G) use this formula, starting in cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow you to
insert and delete rows later as needed without messing up your formulas.

HTH,
Elkar

 

[Pete_UK]

Try this in G7:

=IF(D7="C",IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7),G6)

and copy down as necessary.

I'm not sure why you are checking for E7 and F7 being blank and
returning a blank if they are - wouldn't you want G6 returned in this
case?

Hope this helps.

Pete

 

[Roger Govier]

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

 

[Dave Peterson]

Maybe..

=IF(COUNT(E7:F7)<1,"",G6+F7-E7)
and
=IF(COUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C"))

 

[Gord Dibben]

Not following the thread, but the syntax is incorrect in the last two formulas
Roger posted.

Try these............

=IF(COUNT(E7:F7)<1,"",G6+F7-E7)

=IF(COUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C"))


Gord Dibben MS Excel MVP

 

[Roger Govier]

Hi
Sorry about that. I don't know how I managed to miss out the parentheses
for the IF formula, but I did.
Dave and Gord corrected it for you and all should now be working.

 

[Gord Dibben]

Speaking as one who "misses" often, I can empathize but offer no cure<g>

That's why we hang around here in herds.......for protection.


Gord