Conditional formatting rules (Catching duplicate values)

[Phanindra]

Hi,
I was facing an issue while using Conditional formatting (Top 10% or Bottom 10% of a range).

I have a range of data similar to the following
927
762
7262
8494
450
450

When i try to use the "Bottom 10%" rule to the above data, it highlights the last two values 450, 450.

If I have all the 10 values same in my range. It will highlights all the 10 values. For example.

20
20
20
20
20
20
20
20

Please help me out in understanding how to avoid excel from catching the duplicate values and just format the Bottom 10% of values?

Thanks!

 

[Chihiro]

If you have entire range with same value. All value are basically at the same time belonging to top 10% and bottom 10% (no matter how you slice it, all percentile is comprised of same values).

If there are multiple duplicates, duplicates will belong to same percentile range and will be formatted as same.

I am not sure what you are trying to accomplish here. Can you clarify?

 

[Phanindra]

Hi

492,398
492,398
458,304
458,304
429,806
363,298
294,250
274,723
274,723
274,723
274,723

The above is a part of the real data that I was working on for a one of the spend range 100K - 500K. I want the "Conditional Formatting" to select the "Bottom 10%" of the data in each range. It should format only one of the 274,723. However, In the result i find that all the 274,723 are formatted.
How to overcome this?

 

[Chihiro]

How do you differentiate between each 274,723? Mathematically speaking, all belong in Bottom 10%.