Conditional Formatting?

[lctrues]

I have three columns of dollar amounts. One is an estimated amount, one is an actual amount that will be debited from the estimated amount, and the final column is a balance amount. I need for the balance amount to reflect a 0 balance once the actual equals the estimated instead of building a credit amount.

Thanks

 

[Mark W.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Est.Cost	Ck.Req	Balance
2	$500.00	400	$100.00
3		200	$0.00
4
5
6
7
Sheet2

This message was edited by Mark W. on 2002-10-15 12:49

 

[paliman]

Using MarkW example:

In C2 type

=IF($A$2>SUM($B$2:B2),+$A$2-SUM($B$2:B2)0)

Copy this formula down col. C

 

[lctrues]

I don't need conditional formatting I need an IF nested funtion if actual equals estimates then show zero

 

[Barry Katcher]

Ictrue, Mark's elegant solution IS NOT conditional formating, it's a formula and it allows you to enter any number of check requests to the same item and then go to the next item. No matter how far beyond budget you go on an item, it will always show a balance (remaining) of zero. Isn't this what you asked for?

 

[lctrues]

I will enter his formula asap and see if this is indeed what I need. I must show an active balance until the estimated amount is satisfied and at that time the balance column has to reflect a zero..

tks

 

[Barry Katcher]

Haven't tested it meself, but methinks this is eggzactly what Mark's formula duz.

 

[lctrues]

I can't seem to get it to work..I have to keep all entries on one row per item. Each month that an entry is made it will replace the previous entry and update the balance. THis will happen until the estimated is satisfied and then it should show a zero.

(

 

[Barry Katcher]

Well, that's a horse of a different odor. If it is all on one row, and, if you don't have to enter each check, only a updated total, you only need 3 cells:
In cell A1, the amount of the estimate;
In cell B1, the updated total spent;
In cell C1,
=IF(A1-B1>0,A1-B1,0)

 

[lctrues]

This worked !
Much Appreciated.