"Dan D" wrote in message
...
I'm looking at 100 companies and have their p/e ratios in column B. I
want
to identify the percentile of each p/e ratio in the group and extract the
bottom 30 percentile.

The problem is that for P/E ratios, the "worst" companies in the group
have
a negative p/e ratio (earnings are negative) and the best companies have a
low positive ratio.

So if looking at a set of companies with the following ratios:

10
15
100
-2
-7
8
6
45
9
5

I need a formula that identifies -7 as the worst, -2 as the second worst
and
100 as the third worst. if I run =PERCENTRANK($B$1:$B$10,B1) across all
the
numbers "100" is deemed to be the highest percent rank instead of the
third
lowest.

Thanks!




Try this formula:

=PERCENTRANK(IF(B$1:B$100,1/(B$1:B$10),B$1:B$10),IF(B10,1/B1,B1))

Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER
rather than just ENTER.

Hope this helps / Lars-Åke