Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel PERCENTRANK help | Excel Worksheet Functions | |||
Percentrank Function | Excel Worksheet Functions | |||
PERCENTRANK in array formula: strange behavior | Excel Discussion (Misc queries) | |||
Percentage/PERCENTRANK | Excel Worksheet Functions | |||
what is the underlying formula for the percentrank function | Excel Worksheet Functions |