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?

Hi,

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")Regards

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...

The point here is that all formulae used for Conditional Formatting mustevaluate 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.

I don't know what is going on, I keep getting the error message for the formula: =IF(OR(B23="",B24="",FALSE,IF(($C$24/$C$23)<0.8,FALSE,TRUE))))

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?

For an introduction on what is and is not Conditional Formattinggo here:http://www.contextures.com/xlCondFo...

MIKE

Hi, 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 emptyNote 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

If(A2="","",A2*12)

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*12Look 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 formulaRegards

There are a couple of errors in your construction: =IF(

OR(B23="",B24="",FALSE...You need to close the parenthesis around your OR arguments:

=IF(

OR(B23="",B24=""),FALSE...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.

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

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.

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

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.

Hi, 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:

OR(B23="",B24=""),Regards

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

=IF(AND(B23<>"",B24<>""),IF(($C$24/$C$23)<0.8,FALSE,TRUE))

The AND tells me that I have to have a value in B23

andB24 in order to possibly get a TRUE, but I don't have to manually force a FALSE like the OR function makes me do.

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.

Hi, 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 referencesI see no reason why this formula shouldn't be usable as a formula for conditional formatting, and which willnot get the cell to highlight prematurely.Regards

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History