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

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

"DianneZ" wrote in message
...
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

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


--
Biff
Microsoft Excel MVP


"DianneZ" wrote in message
...
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

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

Regards, :)

Tyro

"T. Valko" wrote in message
...
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...


--
Biff
Microsoft Excel MVP


"DianneZ" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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

See my reply to your other reply.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
What's strange about dividing 39% to 75% into thirds?

Regards, :)

Tyro

"T. Valko" wrote in message
...
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...


--
Biff
Microsoft Excel MVP


"DianneZ" wrote in message
...
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

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
..
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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

Tyro

"T. Valko" wrote in message
...
I *didn't* set the Type for a Percentile rank. I set the Type to Percent.

See my reply to your other reply.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
What's strange about dividing 39% to 75% into thirds?

Regards, :)

Tyro

"T. Valko" wrote in message
...
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...


--
Biff
Microsoft Excel MVP


"DianneZ" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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

"Tyro" wrote in message
t...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

67% of what????

Tyro

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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


"Tyro" wrote in message
t...
67% of what????

Tyro

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

67% of what????

100%

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
67% of what????

Tyro

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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
.
"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"Tyro" wrote in message
t...
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

"DianneZ" wrote in message
...
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

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

"DianneZ" wrote in message
...
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