Excel 2007 - Conditional Formatting Problem with Percentages

[Caros]

I have a problem with conditional formatting when using percentages which I
will try to explain.

I am using "Format all cells based on their value"
Format Style = Icon Sets
Reverse Icon Order is selected.
Red traffic light when value is >= 90 percent
Orange traffic light when < 90 and >= 80 percent
Green traffic light when < 80

All cells have been formatted as percentage.

However the conditional formatting applied is not as expected.
A1 = 50%
Expected result = Green
Actual restul = Green

A2 = 80%
Expected result = Orange
Actual result = Red

A3 = 70%
Expected result = Green
Actual result = Orange

If I change this to numbers, then the results are correct.

Any thoughts appreciated

Caroline

 

[Shane Devenshire]

Hi,

Excel 2007 allows more than one rule to apply, so 1. Order of the rules is
important and 2. After you apple the rules you should select the range and
choose Home, Conditional Formatting, Manage Rules and put a check to the
right of your rules in Stop if True.

 

[Caros]

Thanks for your post, I had already checked that and the order of the rules
is fine.

However I've recreated this problem in a clean spreadsheet with only this
rule i.e no other rules have been defined in this worksheet and I still get
the problem.

So I'm afraid this doesn't help.

 

[Dave P]

According to the Excel help files regarding value types:
"Use a percentage when you want to visualize all values proportionally
because the distribution of values is proportional."
(Type Percent or Percentile will consider all the values included in the
range the conditional formatting is being applied.)
If you want to compare the value of each individule cell to 100%, select the
value type "Number"
Have a great day!

Dave