Conditional Formatting ini Excel 2007

Question

When you initially choose icon sets in Excel 2007 conditional formatting, the settings default to percentages - 67% and 33% -can you please explain eaxctly what these figures relate to. I cannot seem to fathom out what they mean. Please!

Answer 1

When you initially choose icon sets in Excel 2007 conditional formatting, the settings default to percentages - 67% and 33% -can you please explain eaxctly what these figures relate to. I cannot seem to fathom out what they mean. Please!

That's a great question and I had not thought too much about it until reading your post.  But, then I don't use icon sets much either... {grin}

From some preliminary tests it appears the thresholds are calcuated as % * (max - min) + min

Suppose you have a 3 icon set (red, yellow, green), 2 thresholds at 33% and 67%, and the data range from 1 to 14.  Then, the yellow icon will show for a value that is at least 33% *(14 - 1) +1 or 5.29 (and of course, less than the green threshold).  Similarly, the green icon shows for a value that is at least 67% * (14 - 1) +1 or 9.71

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 2

Suppose you have values in a column that run between 0 and 100

0
0
10
10
85
90
100
100
100
100

>=67 percent means that the values must be at least 67% of the largest value, so with the above data 100 is the largest value and 67%*100 = 67 so if the value is >67 it will get a green ball (for example).  In the above example that means the last 6 entries.

<67 and >= 33% means that the value must live between 33 and 67% of the largest value, in the case where the largest value is 100 this translates nicely into values between 33 and 67.  In the above example no items will get a yellow ball.

<33 percent means the values will be less than 33% of the largest value, in this example 33%*100 = 33 so the first four entries get red balls.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 3

Suppose you have values in a column that run between 0 and 100

0
0
10
10
85
90
100
100
100
100

>=67 percent means that the values must be at least 67% of the largest value, so with the above data 100 is the largest value and 67%*100 = 67 so if the value is >67 it will get a green ball (for example).  In the above example that means the last 6 entries.

<67 and >= 33% means that the value must live between 33 and 67% of the largest value, in the case where the largest value is 100 this translates nicely into values between 33 and 67.  In the above example no items will get a yellow ball.

<33 percent means the values will be less than 33% of the largest value, in this example 33%*100 = 33 so the first four entries get red balls.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click theVote as Helpful button. Cheers Shane Devenshire

Dear Shane,

Thank you for your explanation. I validated your explanation using arrow icon sets to to a data set from 1 to 10. Going by the default setting all the values above or equal to 6.7 (in my data set of 1 to 10) should have green up arrow. But when I apply it, value 7 is having a flat yellow arrow with it? Why is that?

Regards

Akash

******@gmail.com

Answer 4

Like data bars and color scales, you can set the value for each of the different categories in an icon set using numbers, percent, percentiles, and formulas. By default, Excel uses percentiles. For example, in the three-icon case, Excel sets 33% and 67% as the break between the three sets of icons.

(Percentile is the value of a variable below which a certain percent of observations fall)

Answer 5

Probably better to ask in the Excel forum

---

John Wilson PowerPoint MVP PowerPoint Alchemy