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?
--
mnwankpah

Select your range

Conditional Foratting
Condition 1
Formula Is =AND($A1<"",$A10.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

"mnwankpah" wrote:

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


"Teethless mama" wrote:

=AND(A1<"",your formula)

"mnwankpah" wrote:

For some reason this doesn't appear to be working...None of the colors are
showing up. Just to clarify, I do have cell with value less than or equal to
0 showing up, I just want the formating to ignore the blank cells. When
using your suggestions, I am selecting a FORMULA instead of a CELL VALUE...is
that okay?
--
mnwankpah


"Max" wrote:

You could use eg an AND(D10, ...) construct
to exclude blank cells which are evaluated as zeros.

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

Cond1: =D130
Format: Red

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

Cond3: =AND(D10,D1<=20)
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mnwankpah" wrote:
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?
--
mnwankpah

Couldn't you just get away with the following, as it does the tests in
order?

Condition 1
Formula Is =AND($A1<"",$A10.8)
format as green

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

Condition 3
Formula Is =$A1<""
format as Red
--
David Biddulph

"Teethless mama" wrote in message
...
Select your range

Conditional Foratting
Condition 1
Formula Is =AND($A1<"",$A10.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

"mnwankpah" wrote:

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


"Teethless mama" wrote:

=AND(A1<"",your formula)

"mnwankpah" wrote:

For some reason this doesn't appear to be working...None of the
colors are
showing up. Just to clarify, I do have cell with value less than or
equal to
0 showing up, I just want the formating to ignore the blank cells.
When
using your suggestions, I am selecting a FORMULA instead of a CELL
VALUE...is
that okay?
--
mnwankpah


"Max" wrote:

You could use eg an AND(D10, ...) construct
to exclude blank cells which are evaluated as zeros.

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

Cond1: =D130
Format: Red

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

Cond3: =AND(D10,D1<=20)
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mnwankpah" wrote:
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?
--
mnwankpah

You could use eg an AND(D10, ...) construct
to exclude blank cells which are evaluated as zeros.

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

Cond1: =D130
Format: Red

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

Cond3: =AND(D10,D1<=20)
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mnwankpah" wrote:
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?
--
mnwankpah

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


"Teethless mama" wrote:

=AND(A1<"",your formula)

"mnwankpah" wrote:

For some reason this doesn't appear to be working...None of the colors are
showing up. Just to clarify, I do have cell with value less than or equal to
0 showing up, I just want the formating to ignore the blank cells. When
using your suggestions, I am selecting a FORMULA instead of a CELL VALUE...is
that okay?
--
mnwankpah


"Max" wrote:

You could use eg an AND(D10, ...) construct
to exclude blank cells which are evaluated as zeros.

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

Cond1: =D130
Format: Red

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

Cond3: =AND(D10,D1<=20)
Format: Green
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mnwankpah" wrote:
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?
--
mnwankpah