Excel - Need Help with Percentrank formula

I am 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$10>0,1/(B$1:B$10),B$1:B$10),IF(B1>0,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

You'll have to adjust your figures before applying any ranking to them.
There are several ways I can think of to do this, one being you could
adjust all positive values:
=IF(B1>0,1/B1,B1)
and rank the results.

This is likely to be misleading though. You say "a formula that
identifies -7 as the worst, -2 as the second worst and 100 as the third
worst" which begins to suggest that ALL negative numbers are worse than
ANY positive number. Can you confim this? If you do, this means that a
company with a value of .00001 is hugely better than a company whose
value is -.00001, yet that difference might only be caused by a few
pennies difference in their profits/turnover, whatever.

A similar question arises: Is a company with a p/e value of -1 really
always going to be worse than a company with a ratio of 10000?

I'd guess that your answers to the questions in both the above
paragraphs might well br 'no'.

What do you really mean when you say "the best companies have a
low positive ratio"?
Arithmetically, this means that the closer to 0 (without going
negative) a company's p/e ratio can get the better that company is, with
zero being the best possible value to have. As soon as a company's p/e
strays into negative territory it becomes the pits. This just reiterates
what was said above.

If on the other hand that phrase means that p/e ratios from 2 to 8
(say), are good and ratios either side of that worsen, then you need to
define what you reckon to be the best, and how quickly quality falls off
either side of that ideal value (and whether it falls away equally
quickly either side of the ideal).

As an aside, I hope the 100 companies you are comparing are all in the
same sector. What is considered to be a healthy p/e ratio in one sector
might be awful in another. (A baked-bean canning factory is going to
have overheads and costs-per-can-produced and costs of distribution more
or less the same per can, regardless of volume produced, whereas a
software house's overheads and costs of production/distibution are not
so related to volume. Once they have written the software (probably where
most cost/effort goes), costs of sale, distribution, packaging are not
very different whether they sell to 10 people or 10,000 - especially if
they sell and distribute via the internet.)


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=166370

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

Thanks Lars & P45cal.
p45scal, with regard to your question, I would agree that having a positive
p/e vs. a negative p/e would not necessarily mean a worse company, just like
a high p/e is not necessarily worse than a low p/e.  Let???s assume however
that you wanted to test whether a company???s p/e is historically a good
predictor of stock returns over the course of the year.  You have the stock
price and the earnings of the universe of companies on January 1st of every
year for the last 20 years.  Wow would you rank the companies by p/e if you
want to compare the ???highest??? quintile to the ???lowest??? quintile?

Well, bearing in mind that p/e is sector dependent, I could only compare
companies in the same sector in this way, and since you probably only
have a few companies in each sector, I would not try to do it with p/e
ratios. If looking for quality companies I'd look at eps trend over the
previous 3 or 4 years, making sure that eps generally went up, and
accelerated up too, over that time frame and then compare returns over
the subsequent year. (Actually, I say this because you mention you have
annual data, I would really do it on a quarterly data looking for
consistent/accelerating eps growth over 5 or 6 quarters.) If I were to
involve p/e ratios in a scan of some sort, I'd look at the p/e ratio
trend making sure it was going down and ensuring that it did not vary
wildly from year to year.

Dan D;600474 Wrote:


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=166370

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]