I'm trying to use conditional formatting to highlight the values that
are in the top 75% of the data set and the bottom 25% of the data set.
Any help would be great. Thanks

Ross


--
Ross Mattson
------------------------------------------------------------------------
Ross Mattson's Profile: http://www.excelforum.com/member.php...o&userid=36757
View this thread: http://www.excelforum.com/showthread...hreadid=564752

If your data is in column A (for example), in cell A1:

In the Conditional Formatting dialog, select "Formula Is". In the adjacent
textbox, enter =PERCENTRANK(A:A,A1,2)<=0.25
Click the Format... button. On the Patterns tab, click a color, then OK.

In the Conditional Formatting dialog, select Add . Select "Formula Is",
and in the adjacent textbox, enter =PERCENTRANK(A:A,A1,2)=0.75
Click the Format... button. On the Patterns tab, click a color, then OK.

Finally, click OK to close the Conditional Formatting dialog. Use the
Format Painter to apply this conditional formatting to other cells as desired.

PERCENTRANK may not give you exactly the results you expect, but it seemed
like the natural choice for your request. It simply converts the rank of each
number to a percentage - i.e.
PRECENTRANK(x) = (rank(x)-1)/(N-1)
...where rank is a number from 1 to N.

Hope this helps,

Hutch

"Ross Mattson" wrote:


I'm trying to use conditional formatting to highlight the values that
are in the top 75% of the data set and the bottom 25% of the data set.
Any help would be great. Thanks

Ross


--
Ross Mattson
------------------------------------------------------------------------
Ross Mattson's Profile: http://www.excelforum.com/member.php...o&userid=36757
View this thread: http://www.excelforum.com/showthread...hreadid=564752