Need Help with Percentrank formula
 

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!

Need Help with Percentrank formula
 

"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