+ Reply to Thread
Results 1 to 9 of 9

Percentrank does not always return a 1

  1. #1
    Registered User
    Join Date
    06-17-2011
    Location
    Huntsville, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    12

    Question Percentrank does not always return a 1

    Hi,
    I'm sorry if this has already been answered, I found a few posts about percentrank but none had this exact problem.
    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
    Last edited by lady_Jane; 07-20-2011 at 09:50 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,069

    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?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    06-17-2011
    Location
    Huntsville, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    12

    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
    Attached Files Attached Files

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    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)
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,069

    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-17-2011
    Location
    Huntsville, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    12

    Smile Re: Percentrank does not always return a 1

    Thank you so much ChemistB,
    That will definitely help.

  7. #7
    Registered User
    Join Date
    06-17-2011
    Location
    Huntsville, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    12

    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. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,069

    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. #9
    Registered User
    Join Date
    06-17-2011
    Location
    Huntsville, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Percentrank does not always return a 1

    That does help, thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1