Conflicting IFERROR and conditional formatting

[blundastruck]

I want a cell to:
1. Remove the "DIV/0" error when input cells are blank
2. When the output cell is above 9%, turn it red.

This is where I am:

With the above example, the output is empty but still turns red. I've tried to flip/flop the rules, but then the cell will not turn red above 9%.

I'm stumped. How do I resolve the conflict?

 

[Joe4]

1. Remove the "DIV/0" error when input cells are blank

Usually, the best way to fix/remove that error is to address the formulas that cause them.

For example, if you have the formula:

=A1/B1

you would get that error whenever B1 is 0 or empty.

So, you could rewrite that formula like this:

=IF(B1<>0,A1/B1,"")

which will return a blank if B1 is blank or 0, otherwise it will do the division.

 

[blundastruck]

The formula does work, but the same issue persists:

 

[jtakw]

Hi,

That's not what @Joe4 meant in Post # 2, he means for you to Fix the Original formula that put the #DIV/0! error in D4 in the first place, then you don't need to deal with the error in CF.
If you post the formula that's in D4, we can fix it for you, or you can just Wrap your current formula in IFERROR, like:

=IFERROR(your current formula,"")

But without doing that, you can use this for CF:

Book3.xlsx
	D
4	#DIV/0!
5	9.50%
6	5%
7	11%
Sheet1068

Cell Formulas
Range	Formula
D4	D4	=1/0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D7	Expression	=IF(ISNUMBER(D4),D4>0.09,0)	text	NO

 

[Joe4]

Hi,

That's not what @Joe4 meant in Post # 2, he means for you to Fix the Original formula that put the #DIV/0! error in D4 in the first place, then you don't need to deal with the error in CF.
If you post the formula that's in D4, we can fix it for you, or you can just Wrap your current formula in IFERROR, like:

=IFERROR(your current formula,"")

But without doing that, you can use this for CF:

Book3.xlsx
	D
4	#DIV/0!
5	9.50%
6	5%
7	11%
Sheet1068

Cell Formulas
Range	Formula
D4	D4	=1/0

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D4:D7	Expression	=IF(ISNUMBER(D4),D4>0.09,0)	text	NO

jtakw is spot on.
Rather than try to address the problem after the fact, it is better to prevent it from happening in the first place (and can usually be done so quite easily).