# Percentrank does not always return a 1

1. ## Percentrank does not always return a 1

Hi,
I have several lists of integer values (not formulas) with many repeating numbers. I want to calculate a percentile value for each of these numbers making sure that all of the repeating numbers have the same percentile value.

I am having two problems:
As an example I have a list of integers ranging from 1 to 16 with many repeating values. When I calculate the percentiles for the various 16s some of them will return 0.98 and some of them will return 0.983, none of them will return 1. Several of my integers lists have the issue of not returning any 1s.

Any ideas?
Thank-you

2. ## Re: Percentrank does not always return a 1

Sounds like a rounding issue. Are you using a rounding function or precision as displayed?

Can you attach a workbook with an example?

3. ## Re: Percentrank does not always return a 1

I didn't think of rounding because I'm using integers.
But I'll play around with it.
Here is an example worksheet.
Thank-you

4. ## Re: Percentrank does not always return a 1

The algorithm for PercentRank is a little obscure. If the number is matched, the result is equal to

=(rank(rng, value, 1) - 1) / (rows(rng) - 1)

So =(RANK(F2, F2:F1117, 1) - 1) / (ROWS(F2:F1117)-1) returns 0.980269058

which is the same value returned by

=PERCENTRANK(F\$2:F\$1117, F2, 15)

5. ## Re: Percentrank does not always return a 1

I have not used PercentRank before but based on the Excel Help, it's equal to # of occurances smaller/(Smaller + larger). So your largest value should always be 1 but that's not the case. I played around with it a little. Check out the table starting in T1.

6. ## Re: Percentrank does not always return a 1

Thank you so much ChemistB,
That will definitely help.

7. ## Re: Percentrank does not always return a 1

If you're up for another challenge I'm trying to figure out how to get that process into one or two formulas rather than 5 columns...

8. ## Re: Percentrank does not always return a 1

I can combine W, X and Y into 1 formula in Z2 dragged down

=SUM(\$V\$1:V1)/(SUM(\$V\$1:V1)+SUM(V3:\$V\$29))

Does that help?

9. ## Re: Percentrank does not always return a 1

That does help, thanks.

There are currently 1 users browsing this thread. (0 members and 1 guests)