Conditional Formats to ignore blank cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to apply a conditional format to a pivot I created. If the value
in the cell meets, exceeds or is below the target it should light up to the
colors of a traffic light. How can I have the conditional format ignore
blank cells? Right now it is highlighting all blank cell (as well as the
title column) as a color for low target?
 
Select your range

Conditional Foratting
Condition 1
Formula Is =AND($A1<>"",$A1>0.8)
format as green

Condition 2
Formula Is =AND($A1<>"",$A1>=0.49,$A1<=0.8)
format as yellow

Condition 3
Formula Is =AND($A1<>"",$A1<0.49)
format as Red
 
Couldn't you just get away with the following, as it does the tests in
order?

Condition 1
Formula Is =AND($A1<>"",$A1>0.8)
format as green

Condition 2
Formula Is =AND($A1<>"",$A1>=0.49)
format as yellow

Condition 3
Formula Is =$A1<>""
format as Red
 
You could use eg an AND(D1>0, ...) construct
to exclude blank cells which are evaluated as zeros.

For example, apply the CF on col D using the formulas

Cond1: =D1>30
Format: Red

Cond2: =AND(D1>20,D1<=30)
Format: Amber

Cond3: =AND(D1>0,D1<=20)
Format: Green
 
Still not working...Here is what I have. I selected the entire colomn in my
forumla since based on the customer you choose, there could be more than one
row that this applies too based on how many sites the customer has. If the
"Inventory turns" are greater than 80% = Green; if they are between 49-80%=
Yellow; less than 49% = Red.
 
Back
Top