conditional formatting 2007

[DianneZ]

Distribution Capacity
Atlantic 75%
Southwest 73%
Northwest 69%
North Central 54%
Mountain West 51%
Northeast 47%
Central 41%
Midwest 40%
Southeast 39%
the results of conditional formatting don't make sense to me,
on the example provided above :
if I apply "Icon sets" that look like the traffic lights
it applies green when value is >= 67 %
applies yellow when value is <67% and >= 33%
applies red when <33%

But in fact the last four values on the list show as red????
In my estimation only the last value should show as red...
Am I missing something here??
Thanks!
Dianne

 

[Tyro]

The range you presented to Excel is from 39% to 75%. The upper 1/3 of this
range is from approximately from 63% to 75%, the middle 1/3 from 50% to 62%
and the lower 1/3 is from 39% to 50%. So anything from 63% and above is in
green, from 50% to 62% in yellow and from 39% to 50% in red. All figures are
approximate. I'm too tired to compute this to the nth degree of accuracy.
Excel did it for us. Excel is not a mind reader. It does not know that
you think that your range is based on 1% to 100%. Excel takes it as you
presented it from 39% to 75% and divides by 3.

Tyro

 

[T. Valko]

I can verify that behavior but I've only been using 2007 for a couple of
weeks and haven't yet discovered all the new "strange, unexplained"
happenings!

I did get it to work properly if you set the Type as NUMBER and then use
decimals as the boundaries:

=0.67
=0.33

But, using your original settings add new entries to the list in column B
and see what happens:

B11 = 30%
B12 = 25%
B13 = 20%
B14 = 2%

WTH? (what the heck?)

Hmmm...

 

[Tyro]

What's strange about dividing 39% to 75% into thirds?

Regards,

Tyro

 

[T. Valko]

What you describe is a percentile rank.

With icon sets there are 4 Types to choose from:

Number
Percent
Formula
Percentile

The behavior the OP describes is happening when I use Type Percent. This
doesn't seem correct (to me). If we set Type as Percentile then what you
describe should be the applied boundaries but this shouldn't apply to Type
Percent.

 

[T. Valko]

I *didn't* set the Type for a Percentile rank. I set the Type to Percent.

See my reply to your other reply.

 

[Tyro]

Sorry. Excel takes the range (key word!) you present, in this case 39 to 75
and divides it by 3 to determine the percent of the range <=33%, 34% to 66%,

=67% and applies those percentages to the range. The range from 39 to 75,

not 1 to 100.

Tyro
..

 

[Tyro]

That does not change the fact that Excel works with the range, in this case
39% to 75%.

Tyro

 

[T. Valko]

From Excel help:

Format a percentage Select Percent.
Valid values are from 0 (zero) to 100. Do not enter a percent sign.

Use a percentage when you want to visualize all values proportionally
because the distribution of values is proportional.

So, that's what Percent means!

The way that it's "presented" in the userform is very misleading. I'm sure
most users would think it means just as myself and the OP thought it meant.

So, set Type to NUMBER and use decimals as the boundaries.

 

[Tyro]

In conditional formatting as in anywhere else in mathematics, percent
applies to the range of numbers to which it applies. For example if I have a
range of numbers from 6 to 14, the lower 33% are the numbers 6, 7, 8 and the
middle 33% are the numbers 8, 10, 11 and the upper 33% are the numbers 12,
13 and 14.

Tyro

 

[Tyro]

Correction: Once again, I never see my mistakes until after I post. I can
read this stuff 1,000,000 times before posting and not see my error.

In the range of numbers from 6 to 14 the lower 33% are 6, 7 and 8, the
middle 33% are 9, 10, 11 and the upper 33% are 12, 13 and 14. Percent
applies to the range of numbers under discussion, not all the numbers in the
world.

Tyro

 

[T. Valko]

Well, that's not the first thing I think of when I see:

= ... 67 ... Percent

I think: if the cell value is >= 67% apply this format.

 

[Tyro]

67% of what????

Tyro

Well, that's not the first thing I think of when I see:


I think: if the cell value is >= 67% apply this format.

 

[Tyro]

For example in the range 39-75, 71 is (71-39 +1) / (75 -39 +1) = 89% of the
range. Just as in the range 1 - 100, 51 is (51-1 +1) / (100 - 1 +1) = 51%
of the range

Tyro

 

[T. Valko]

67% of what????

100%

 

[Tyro]

Well, the 39% should appear as yellow, not red as red applies only to cells
<33%. What I did is put 100 % in a cell, 50% in the cell below it and 0% in
the cell below that and hid the percents with a custom format of ;;; (3
semicolons). Then I included the 3 cells with your range. Excel made the
percents from 69% and higher have the green icon and the cells from 39%
through 54% have the yellow icon. The 100% cell had the green icon, the 50%
cell the yellow icon and the 0% cell the red icon. Then in those 3 cells to
their right I entered >67%, 34% to 67% and <33% to act as a legend for your
range. Excel is treating the icons just as it does the data bars and color
scales; that is, it is comparing the cell contents with other cells in the
range to determine the result. It is not comparing icon percents with 0%
through 100% which is why it is disconcerting. Perhaps in the future, MS
will make the percent selection in the rules mean just that. And to
accomplish that, they'll probably have to put a new option in the Excel
options to allow for that so as to not disturb existing workbooks.

Tyro
..