Conditional Formatting-Formula

Microsoft Excel 2003 (full product)
September 30, 2009 at 08:03:45
Specs: Windows XP
I have two rows and three columns of cells A1:C2.

In the first row: A1:B1, a user inputs two areas
Cell C1 is conditionally formatted so that if B1 is 20% larger than A1, the cell highlights yellow.

The second row: A2:B2: a user inputs how much of the area is actually used. Cell C2 is conditionally formatted so that if B2/A2 is less than 70%, the cell highlights yellow.

My issue is that when a user enters a value in cell A1 and B1, Cell C2 automatically is highlighted yellow, (there is not yet an input in cell B2). How can I format the cell so that it doesn't highlight until it has the two values necessary (cell B2 and A2) to make that calculation?

See More: Conditional Formatting-Formula

Report •

September 30, 2009 at 08:51:57
Just nest your existing IF statement inside an IF statement that tests if one or other or both cells are empty and returns FALSE.

For example:
=IF(OR(B1="",B2=""),FALSE,"your existing formula")


Report •

September 30, 2009 at 09:16:09
Thanks. Can this work for cells that have formulas?

For example, if Cell A1 has a formula = A2*12, how can I make sure to test for an "empty cell." I tried writing:

If(A2="","",A2*12), but that doesn't work...

Report •

September 30, 2009 at 10:19:34
The point here is that all formulae used for Conditional Formatting must evaluate to either TRUE or FALSE.

When Excel evaluates the formula to TRUE, it applies the format for that condition. If the formula evaluates to FALSE then that formatting is turned off.

Formulae that evaluate to a number or text value ("") are not TRUE or FALSE so they can't be used with CF.

Think about the conditions that will result in TRUE or FALSE and write your formula to obtain one of those 2 results.

Report •

Related Solutions

September 30, 2009 at 10:37:17
I don't know what is going on, I keep getting the error message for the formula:


If I remove a parenthesis, it's not enough. If I add one, there is still an error.

I removed the FALSE, TRUE...error.

Removed the parenthesis around my formula...error.

You don't have to tell me how to write the formula, but which part of my formula is not right?

Report •

September 30, 2009 at 10:38:33
For an introduction on what is and is not Conditional Formatting go here:


Report •

September 30, 2009 at 10:47:00

IF uses the value in the cell and not the formula in the cell.

A cell with a formula that returns 0 is zero
A cell with a formula that returns "" is empty

Note that a cell with a formula like =A2*12 will not return empty, it will return 0.
Try putting the formula in a cell, and then go to A2 and hit delete.
The formula will return 0 not empty. Put 2 in A2 and the formula returns 24.
So your test is likely to be a test for 0 not a test for empty.

the formula you just gave
1. is testing if cell A2 is empty.
2. If cell A2 is empty, the formula returns empty.
3. If cell A2 is not empty it returns the value in A2*12

Look at the help information for the IF statement - the only part of the IF statement that makes a decision is the first part, and then one of the two remaining parts of the function is calculated.

I presume that this formula If(A2="","",A2*12)
is not being used as part of a conditional format formula


Report •

September 30, 2009 at 10:51:56
There are a couple of errors in your construction:


You need to close the parenthesis around your OR arguments:


Once you've done that, the only real error is the extra parentheses at the end. You only need 2 to close both IF statements.


That said, the parentheses around ($C$24/$C$23) are not needed as Excel will evaluate that quotient before checking to see if it is <0.8. They won't cause an error, but they are not needed.

This formula will not result in an error. Whether it actually produces the results you want, only you can say.


Report •

September 30, 2009 at 10:59:35
Derby Dad, you hit it right on the nail. It solved my error message problem, but didn't produce the results that I wanted. I think I'll move on to another portion of the form. If you guys can come up with any ideas on how to not get the cell to highlight prematurely it would be greatly appreciated. Thanks.

Report •

September 30, 2009 at 11:00:31

The formula =IF(OR(B23="",B24="",FALSE,IF(($C$24/$C$23)<0.8,FALSE,TRUE))))

is not the same as the one I gave you.

There needs to be a parenthesis around the OR statement:


Report •

September 30, 2009 at 11:07:56

I believe this formula will give you the same results but in a manner that I find a little easier to understand:


The AND tells me that I have to have a value in B23 and B24 in order to possibly get a TRUE, but I don't have to manually force a FALSE like the OR function makes me do.

Report •

September 30, 2009 at 11:19:22
What do you mean by "not get the cell to highlight prematurely"?

Are you aware the formula that we "fixed" for you doesn't even match the requirements in your original post?

In your OP, the user inputs values into 2 cells and then the CF is supposed to be based on a mathematical operation performed on those same 2 cells.

However, the formula we "fixed" for you, checks to see if 2 cells contain data, but then performs a mathamatical operation on 2 totally different cells.

I'm not concerned that you changed the specific cells you are asking about, but that the situation is totally different since you are no longer performing the operation on the same pair of cells that the user entered the data in.

You're confusing us - well, at least me.

Report •

September 30, 2009 at 12:23:50

I see that the formula you asked for help on fixing, refers to different cells to the original post.

The original input cells that needed to be tested for 'empty' were B1 and B2.

The fixed formula either ( ) around OR or DerbyDad03s version with AND will test cells B23 and B24.

Is that what you want.

Once you have the right cell references I see no reason why this formula shouldn't be usable as a formula for conditional formatting, and which will not get the cell to highlight prematurely.


Report •

Ask Question