# Conditional Formatting?

#### lctrues

##### Board Regular
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
And, what is the balance amount when they're not equal?

The balance amount will be the difference between actual and estimated until a zero balance is met...

How is the actual amount being changed? Is it just someone punching in numbers or does it calculate the value from elsewhere on the spreadsheet?

Do you just want to use the third column to warn people if the actual amount is equal to or exceeds the estimated value?

If I understand you correctly, you have to use a formula to calculate your balance.

Say col. A is the estimated amount, col. B the actual amount and col. C your balance.

In C2 enter and copy down:

=SUM(\$A\$1:A1)-SUM(\$B\$1:B1)

And this will give you the balance after each movement

Is this what you want?

The spreadsheet calculates the balance. Now I have been asked to add the additional formatting that will stop the balance once it reaches zero and will show the zero balance in the column. As it is set up now it will continue to calculate a balance each time a new amount is added.

Provide some sample data (including cell references), and indicate the desired results.
This message was edited by Mark W. on 2002-10-15 12:07

=SUM(\$F12:\$F200)
F 12
F12:F200
=\$D12-\$E12

D12-no formula
E12-no formula
This message was edited by lctrues on 2002-10-15 12:23

And, the data? Desired results?

Est. Cost
(Cr, Rpt.#)
(1)\$500.00
(
Ck. Req.
(Trial Bal.)
(1st entry)\$400.00
(2nd entry)\$200.00
Balance
(Est.Liab.)
=\$D12-\$E12
\$`100.00 balance
\$this column should then reflect a 0 balance because the actual charges have depleted the estimated amount. They do not want to show anything above and beyond the estimated amount

Replies
2
Views
254
Replies
9
Views
299
Replies
5
Views
264
Replies
1
Views
107
Replies
1
Views
139

1,219,906
Messages
6,150,908
Members
450,991
Latest member
ExcelDoer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back