Substract in a condional formating cell

**Kanibalu** · 02-21-2012, 11:36 AM

Hi there, I am new to this forum chat.
I have a cell which I made a conditional formating to be highlighted if amount is less than another amount for example:
if A1 is greater than B1 it changes to red (I used conditional formating, Highlight Cell Rules- Less Than). So if in B1 i have an amount of 500 and in A1 ihave an amount of 400 this will change in red)
now I need to deduct cell C1 with cell D1=and have the answer inputted in A1) When I tried to input formula =C1-D1 (in cell A1) it did not work

Any one can help please

Thank you

**NBVC** · 02-21-2012, 11:39 AM

In what way did it "not work"? Is it wrong answer? Did it produce an error (which error)? Did it not highlight as expected?

**Kanibalu** · 02-21-2012, 11:55 AM

untitled.jpg

Thanks for helping i attached a print screen of sheet.

**NBVC** · 02-21-2012, 11:58 AM

it means you have a circular reference. This means that the formulas cells are referencing each other. Do you have a formula in L8? What is it?

**Kanibalu** · 02-21-2012, 12:56 PM

Yes i have a forumla to automaically inputs date in cell D8 formula is: =D8

**NBVC** · 02-21-2012, 01:03 PM

That is what is creating the circular reference then... in L8 you have =D8 and in D8 you have =L8-M8... so the D8 and L8 are creating the circular reference.

If you are sure that is what you want, then go to the Office Button, click Excel Options, select Formulas and then in the right pane check Enable Iterative Calculation from the top section. Click Ok.