Conditional Formatting with multiple Conditions

Question

I am trying to have conditional formatting to flag a cell RED when cells meet one of the following conditions. number is less than 90% of Target, number is greater than 110% of target, or there's a number when there was no target. This is my current formula in conditional formatting. =OR(AND(NOT(ISBLANK($E3)),$D3=""),$E3<(90%$D3),$E3>(110%$D3))

I can get AND(NOT(ISBLANK($E3)),$D3="") to work by itself, however when I add OR(...,$D3=""),$E3<(90%$D3)$E3>(110%$D3))

only the latter half of the formula will work.image.png

Answer 1

It looks like you might be missing the multiplication operator with percentages. It should be 90%$D3 and 110%$D3, not 90%$D3. You should also guard the % comparisons so they only run when a target exists, otherwise blanks/zeros can interfere.

Use this as your conditional formatting formula:

=OR(AND($E3<>"",$D3=""),AND($D3<>"",OR($E3<0.9*$D3,$E3>1.1*$D3)))

What this does:

AND($E3<>"",$D3="") Flags red when there is an Actual but no Target.

AND($D3<>"",OR($E3<0.9*$D3,$E3>1.1*$D3)) Flags red when there is a Target and the Actual is <90% or >110% of it.

Apply this rule to your Actual range (for example E3:E100).

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin