Conditional Formatting?

lctrues

Board Regular
Joined
Oct 13, 2002
Messages
103
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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

lctrues

Board Regular
Joined
Oct 13, 2002
Messages
103
The balance amount will be the difference between actual and estimated until a zero balance is met...
 

ThePencilQueen

Board Regular
Joined
Jun 26, 2002
Messages
109
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?
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254

ADVERTISEMENT

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?
 

lctrues

Board Regular
Joined
Oct 13, 2002
Messages
103
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.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

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
 

lctrues

Board Regular
Joined
Oct 13, 2002
Messages
103
=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
 

lctrues

Board Regular
Joined
Oct 13, 2002
Messages
103
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
 

Forum statistics

Threads
1,143,641
Messages
5,719,988
Members
422,257
Latest member
Calion

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?

Disable AdBlock

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
Top