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
