A Question Concerning The Percentile Rank Function In Excel 2007

[Hack]

I have a two part question concerning Excel’s PercentileRank function.

1. How do I deal with #N/A returns? Every example I’ve found through Google searches provides code for dealing with a #N/A return when using VLOOKUP.

2. Most importantly, why do I get that at all? I have thirteen columns representing metrics and I need to percentrank. I have a total of 2, 438 rows for the thirteen different metrics (which in the real world represents individual medical practices)

Some columns will give me a valid return for all 2,438 rows and some will give me #N/A for all 2,438 rows. I’ve attached a sample which represents my problem.

Is this a data issue or a lack of understanding on my part regarding how PercentileRank works issue?

[vbfbryce]

Hack,

I believe you are getting N/A when the range of your data in any given column does not include the number 2, since that's what your formula is trying to rank.

I'm thinking you want this instead:

=PERCENTRANK(myrange,A4)

so you can rank the value in a given cell compared to your overall list.

Make sense?

vBryce

[westconn1]

as i am not totally sure of your desired result, i may be totally incorrect, but it would appear to me that always asking for the rank position of 2, for each row, in a reducing range is probably not going to produce an appropriate result

at a brief look, column A values are all less than 2, so no result, changing any one cell to a value greater than 2 will give actual values for the formula, where referencing the changed cell, as the change cell is the only one above 2, the formula result would always be 1.00

[Spoo]

Hack

You reference Excel 2007 and the function PercentileRank()

I've only got 2003, and there is no such function.
The closest I come is Percentile() or PercentRank(),
so no soup for me.

If you can't get PercentileRank() to work properly, perhaps you could
write your own function. To my mind, such a function would

1. group by some specified percentile segment .. say 10% .. so
   
   • lowest would be the 10-percentile
   • highest would be the 90-percentile
2. then rank the results within each percentile
   
   • say the 10-percentile has 100 hits .. ranks would be 1 to 100
   • say the 90-percentile has 324 hits .. ranks would be 1 to 324

Spoo

[anhn]

With function PERCENTRANK(array, x, [significance]):
If x < Min(array) or x > Max(array), the function will return #N/A.
In other words, you must have Min(array) <= x <= Max(array).

To deal with #N/A, you can use IF() to test:

=IF(MIN($A4:$A$2436) > 2, "Too Low", IF(MAX($A4:$A$2436) < 2, "Too High", PERCENTRANK($A4:$A$2436, 2)))

or

=IF(ISNA(PERCENTRANK($A4:$A$2436, 2)), "Out of range", PERCENTRANK($A4:$A$2436, 2))

or with Excel-2007+

=IFERROR(PERCENTRANK($A4:$A$2436, 2), "Out of range")

Replace "Too low", "Too high" and "Out of range" with whatever you want the function to return in those cases.

[Spoo]

Anhn

Nicely done ..

Open question in my mind.. was PercentileRank()
a typo, or does that function actually exist in 2007+ ?

Spoo

[Hack]

@Spoo: You are right...it is PERCENTRANK - these business types around here refer to it as a "percentile ranking" and I think I got "asimalated"

@anhn: Thanks...I did find that IF statement (eventually) that assisted me in dealing with the #N/A problem.

@vbfbryce & westconn1: The rank positioning thing seems to have been what was causing me all my angst. I've got more testing to do, but I think your suggestions were just the ticket.