Find the 10% value

[Rich]

Hi,

I have a monthly report that contains data for a varying amount (each month)
of users across several column.

For each column I need to use conditional formatting to highlight the worst
10% for each measure.

Currently I'm doing this manually by sorting the column ascending, then if
there are 15000 users, look at the value corrensponding to line 1500, enter
this in a cell which I then reference in my conditional format.

What the best way to do this by formula?

 

[Ron Rosenfeld]

Hi,

I have a monthly report that contains data for a varying amount (each month)
of users across several column.

For each column I need to use conditional formatting to highlight the worst
10% for each measure.

Currently I'm doing this manually by sorting the column ascending, then if
there are 15000 users, look at the value corrensponding to line 1500, enter
this in a cell which I then reference in my conditional format.

What the best way to do this by formula?

For a conditional formatting formula, that will highlight the lowest 10% of
values in a range, you could try this:

Assuming your data is in column E:

=AND(LEN(E1)>0,E1<=SMALL(OFFSET(E1,0,0,COUNT(E:E),1),COUNT(E:E)*10%))
--ron

 

[Joe User]

For each column I need to use conditional formatting
to highlight the worst 10% for each measure.

Try the following condition format in column A or in A1:A1500:

"cell value is" "less than or equal to"
=PERCENTILE(A$1:A$1500,10%)

The relative "A" and absolute "$1" and "$1500" should facilitate copying
conditional format across the several columns.


----- original message -----