Need Help with Percentrank formula

[Dan D]

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!

 

[Lars-Ã…ke Aspelin]

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$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

 

[p45cal]

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 coul
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 tha
identifies -7 as the worst, -2 as the second worst and 100 as the thir
worst" which begins to suggest that ALL negative numbers are worse tha
ANY positive number. Can you confim this? If you do, this means that
company with a value of .00001 is hugely better than a company whos
value is -.00001, yet that difference might only be caused by a fe
pennies difference in their profits/turnover, whatever.

A similar question arises: Is a company with a p/e value of -1 reall
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 abov
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 goin
negative) a company's p/e ratio can get the better that company is, wit
zero being the best possible value to have. As soon as a company's p/
strays into negative territory it becomes the pits. This just reiterate
what was said above.

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

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

 

[Dan D]

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?

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're 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've 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

Microsoft Office Help

.

 

[p45cal]

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

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

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

Forums' (http://www.thecodecage.com/forumz/showthread.php?t=166370)

'Microsoft Office Help' (http://www.thecodecage.com)

.