We help IT Professionals succeed at work.

# How Excel calculates the top 10% of values in Conditional Formatting

on
I want to get a better understanding of the "top 10%" feature in Conditional Formatting in Excel - this is my understanding, it takes the full range and identifies the entries that correspond to the highest 10% in value i.e. the products with prices that fall into the top 10% of the range. Or is it something else?
Comment
Watch Question

## View Solution Only

Consultant
CERTIFIED EXPERT

Commented:
Analyst Assistant
CERTIFIED EXPERT

Commented:
I believe percentiles are used to calculate the top-n%.
Consultant
CERTIFIED EXPERT

Commented:
This example might help clarify.
EE11.xlsx
Senior Developer
CERTIFIED EXPERT
Commented:
The TOP n and TOP p % need to identify values to highlight cells even in an unordered range. Thus both methods look at the range and identify these values. Then they are used for comparison. The exact logic (or difference) is observable, when it needs to handle ties:

1) TOP 40% of 5 items = 2 items. The TOP 2 items are [ 5, 4 ] => { 5, 4 }.  Now all these values are marked:
A) As the occurrence of each values in the range is exactly one, two cells are highlighted.
B) As the occurrence of each values in the range is not exactly one, all 5's and 4's are marked. Cause it can not distinguish the ties.

1a) TOP 40% of 10 items = 4 items. The TOP 4 items are [ 5, 5, 4, 4 ] => { 5, 4 }.  Now all these values are marked:
C) Using more items, gets us also ties on 4.

2) TOP 2 of n items = 2 items. The TOP 2 items are [ 5, 4 ] => { 5, 4 }.
E) As the occurrence of each values in the range is exactly one, two cells are highlighted.
F) As the occurrence of each values in the range is not exactly one, all 5's and 4's are marked. Cause it can not distinguish the ties.

2a) TOP 2 of n items = 2 items. The TOP 2 items are [ 5, 5 ] => { 5 }.
G) As the occurance of the highest value is 2, marking only 5's is sufficient. Cause it can not distinguish the ties.

[] indicate the values in the range. {} indicate the values used for marking.

TOP n should not be mixed with
- count of ordered elements (median-style)

TOP p% should not be mixed with
- elements which value are larger than p% of the range total
- elements which value sum to the first p% of the total sum (Pareto-style)

Commented:
Thank you for that explanation - has really helped to clarify things!