Percent Rank

[emcote]

Please explain the definition of this function in a data set. When I perform
the same function on the data in Quatro Pro or by hand, I get different anwer!

 

[Michael R Middleton]

emcote -

Please explain the definition of this function in a data set. When I
perform the same function on the data in Quatro Pro or by hand, I get
different anwer! <

Different authors, and perhaps different spreadsheets, use different methods
for finding percentiles and ranks, particularly the treatment of endpoint
values and the treatment of ties.

See the Knowledge Base article "PERCENTRANK() May Appear to Return Incorrect
Results" at

http://support.microsoft.com/default.aspx?scid=kb;en-us;129436

- Mike

www.mikemiddleton.com

 

[emcote]

Thanks. So if Excel gives me a percent rank of 43% for one of the data
points (where there are multiple equal values in the set), how do I describe
the significance of this in an analysis report to my boss. What I had been
looking for is a way to say x value is greater than or equal to x% of values
in the list. Is there a different function I should use?

Beth Cote

 

[Michael R Middleton]

Beth -

If you need to report on only one or several values, you could avoid using a
worksheet function. Instead, make a copy of the list, sort it, put rank
numbers 1,2,3,... in an adjacent column, also put cumulative percents
1/n,2/n,3/n,... in another adjacent column if needed, and use these lists to
make your report. I see some advantages to this approach: you don't have to
worry about trying to understand the way PERCENTRANK treats ties, you will
have a very complete understanding of how you obtained your results, and
you'll be very confident about reporting to your boss and answering any
questions he or she may have.

- Mike