Ranking Functions in Excel

Excel Functions: Figure 1 summarizes the various ranking functions available in all versions of Excel for a data set R. We describe each of these functions in more detail in the rest of this webpage, plus we describe additional ranking functions that are only available in Excel 2010/2013.

Excel ranking functions

Figure 1 – Ranking functions in Excel

Observation: All these functions ignore any empty cells and cells with non-numeric values.

MIN and MAX

Definition 1: MIN(R) = the smallest value in R and MAX(R) = the largest value in R

Example 1: For range R with data elements {4, 0, -1, 7, 5}

  • MIN(R) = -1
  • MAX(R) = 7

SMALL and LARGE

Definition 2: SMALL(n, R) = the nth smallest value in R and LARGE(n, R) = the nth largest value in R. Here n can take on any value from 1 to the number of elements in R, i.e. COUNT(R).

Example 2: For range R with data elements {4, 0, -1, 7, 5}

  • LARGE(R, 1) = 7, LARGE(R, 2) = 5, LARGE(R, 5) = -1
  • SMALL(R, 1) = -1, SMALL(R, 2) = 0, SMALL(R, 5) = 7

Observation:

  • SMALL(R, n) = LARGE(COUNT(R) – n + 1, R)
  • LARGE(R, n) = SMALL(COUNT(R) – n + 1, R)

RANK

Definition 3: RANK(c, R, d) = the rank of data element c in R. If d = 0 (or is omitted) then the ranking is in decreasing order, i.e. a rank of 1 represents the largest data element in R. If d ≠ 0 then the ranking is in increasing order and so a rank of 1 represents the smallest element in R.

Example 3: For range R with data elements {4, 0, -1, 7, 5}

  • RANK(7, R) = RANK(7, R, 0) = 1
  • RANK(7, R, 1) = 5
  • RANK(0, R) = RANK(0, R, 0) = 4
  • RANK(0, R, 1) = 2

Observation:

  • If SMALL(R, n) = c then RANK(c, R) = RANK(c, R, 0) = n
  • If LARGE(R, n) = c then RANK(c, R, 1) = n
  • RANK(c, R) + RANK(c, R, 1) = COUNT(R) + 1
  • For any value c and d, 1 ≤ RANK(c, R, d) ≤ COUNT(R)

Observation: Excel’s RANK function does not take care of ties very well. E.g., if the range R contains the values {1, 5, 5, 0, 8}, then RANK(5, R) = 2 because 5 is the 2nd highest ranking element in range R. But 5 is also the 3rd highest ranking element in the range, and so for many applications it is useful to consider the ranking to be 2.5, namely the average of 2 and 3.

To correct for ties for any data element c in the range R, you can use the following formula (see Built-in Excel Functions for a description of the COUNTIF function):

= RANK(c, R) + (COUNTIF(R, c) – 1) / 2

Excel 2010/2013 Function: Excel 2010/2013 address this issue by providing a new function RANK.AVG which has the same arguments as RANK but returns the average of equal ranks as described above. Excel 2010/2013 also provide the function RANK.EQ which is equivalent to RANK.

Real Statistics Function: For Excel 2007 users the Real Statistics Resource Pack provides the supplemental function RANK_AVG which is equivalent to RANK.AVG.

Example 4: Using the RANK.AVG or RANK_AVG functions find the ranks of the data in range E17:E23 of Figure 2.

Average ranking

Figure 2 – Average ranking

The result is shown in column F of Figure 2. E.g. the average rank of 8 (cell E21 or E22) is 1.5, as calculated using the formula =RANK_AVG(E21,E17:E23) as shown in cell F21 (or F22). If instead you want the ranking in the reverse order (where the lowest value gets rank 1) then the results are shown in column G. This time using the formula =RANK_AVG(E21,E17:E23,1) we see that the rank of 8 is 6.5 as shown in cell G21.

Excel Data Analysis Tool: In addition to the functions described above, Excel also provides a Rank and Percentile data analysis tool. This tool uses the Excel 2007 definitions of rank and percentile.

Example 5: Calculate the rank and percentile for the following data (using the Excel 2007 definitions of rank and percentile).

Scores Excel

Figure 3 – Data for Rank and Percentile data analysis tool

For the above data, the tool generates the table in Figure 4.

Rank percentile analysis tool

Figure 4 – Output from Rank and Percentile data analysis tool

The table is sorted in rank order. Point indicates the index of the corresponding score in the input data range. E.g. 94 is the 5th data element in the input range, but is the largest data element and so has rank 1.

PERCENTILE

Definition 4: For any percentage p (i.e. 0 ≤ p ≤ 1 or equivalently 0% ≤ p ≤ 100%), PERCENTILE(R, p) = the element at the pth percentile This means that if PERCENTILE(R, p) = c then p% of the data elements in R are less than c.

If p = k/(n–1) for some integer value k = 0, 1, 2, … n–1 where n = COUNT(R), then PERCENTILE(R, p) = SMALL(R, k+1) = the k+1th element in R. If p is not a multiple of 1/(n–1), then the PERCENTILE function performs a linear interpolation as described in the examples below.

Example 6: For range R with data elements {4, 0, -1, 7, 5}, the 5 data elements in R divide the range into 4 intervals of size 25%, i.e. 1/(5-1) = .25. Thus

  • PERCENTILE(R, 0) = -1 (the smallest element in R)
  • PERCENTILE(R, .25) = 0 (the second smallest element in R)
  • PERCENTILE(R, .5) = 4 (the third smallest element in R)
  • PERCENTILE(R, .75) = 5 (the fourth smallest element in R)
  • PERCENTILE(R, 1) = 7 (the fifth smallest element in R)

For other values of p we need to interpolate. For example

  • PERCENTILE(R, .8) = 5 + (7 – 5) * (0.8 – 0.75) / 0.25 = 5.4
  • PERCENTILE(R, .303) = 0 + (4 – 0) * (0.303 – 0.25) / 0.25 = .85

Of course, Excel’s PERCENTILE function calculates all these values automatically without you having to figure things out.

Excel 2010/2013 Function: Excel 2010/2013 introduce an alternative version of the percentile function named PERCENTILE.EXC.

If n = COUNT(R), then for any integer k with 1 ≤ kn,

PERCENTILE.EXC(R, k/(n+1)) = SMALL(R, k), i.e. the kth smallest element in R

For 0 < p < 1, if p is not a multiple of 1/(n + 1),then PERCENTILE.EXC(R, p) is calculated by taking a linear interpolation between the corresponding values in R. For p < 1/(n+1) or p > n/(n+1), no interpolation is possible, and so PERCENTILE.EXC(R, p) returns an error value.

Excel 2010/2013 also provide the new function PERCENTILE.INC which is equivalent to PERCENTILE.

Example 7: Find the 0 – 100 percentiles in increments of 10% for the data in Figure 3 using both PERCENTILE (or PERCENTILE.INC) and PERCENTILE.EXC.

The result is shown in Figure 5. E.g. the score at the 60th percentile is 58 (cell Z10) using the formula =PERCENTILE(B3:M3,Y10), while it is 59 (cell AC10) using the formula =PERCENTILE.EXC(B3:M3,AB10).

Percentile calculation Excel

Figure 5 – PERCENTILE vs. PERCENTILE.EXC

PERCENTRANK

Definition 5: PERCENTRANK(R, c) = the percentile of data elements up to c. If PERCENTRANK(R, c) =  p then PERCENTILE(R, p) = c.

Example 8: For range R with data elements {4, 0, -1, 7, 5}

  • PERCENTRANK(R, 5) = .75
  • PERCENTRANK(R, 5.4) = .8

For any value c in the range R, you can calculate PERCENTRANK(R, c) as the number of elements in R less than c divided by the number of elements in the R less 1. Since R contains 5 elements of which 3 elements are less than 5 (namely -1, 0 and 4), we know that PERCENTRANK(R, 5) = 3/(5-1) = .75.

In a similar manner, we see that PERCENTRANK(R, 7) = 4/(5-1) = 1. Since 5.4 is not in range R but is between the elements 5 and 7, which are in R,  PERCENTRANK(R, 5.4) is calculated as a linear interpolation between .75 and 1, namely

image107x

Observation: You can also add a 3rd argument which represents the number of significant figures in the answer. Thus PERCENTRANK(R, .85, 5) = .30312

Excel 2010/2013 Function: Excel 2010/2013 introduce an alternative version of the percent rank function named PERCENTRANK.EXC. This function is defined so that if PERCENTRANK.EXC(R, c) =  p then PERCENTILE.EXC(R, p) = c.

Excel 2010/2013 also provide the new function PERCENTRANK.INC which is equivalent to PERCENTRANK.

Example 9: Repeat Example 5 using the PERCENTRANK (or PERCENTRANK.INC) and PERCENTRANK.EXC functions.

The result is shown in Figure 6. E.g. the score 45 (cell T12) is at the 27.2th percentile (cell V12) using the formula =PERCENTRANK(T4:T15,T12), while it is at the 30.7th percentile (cell W12) using the formula =PERCENTRANK.EXC(T4:T15,T12).

Percentile rank calculation Excel

Figure 6 – PERCENTRANK vs. PERCENTRANK.EXC

QUARTILE

Definition 6: For any integer n = 0, 1, 2, 3 or 4, QUARTILE(R, n) = PERCENTILE(R, n/4). If c is not an integer, but 0 ≤ c ≤ 4, then QUARTILE(R, c) = QUARTILE(R, INT(c)).

Observation:

  • QUARTILE(R, 0) = PERCENTILE(R, 0) = MIN(R)
  • QUARTILE(R, 1) = PERCENTILE(R, .25)
  • QUARTILE(R, 2) = PERCENTILE(R, .5) = MEDIAN(R)
  • QUARTILE(R, 3) = PERCENTILE(R, .75)
  • QUARTILE(R, 4) = PERCENTILE(R, 1) = MAX(R)

Example 10: For range R with data elements {4, 0, -1, 7, 5}

  • QUARTILE(R, 0) = PERCENTILE(R, 0) = -1
  • QUARTILE(R, 1) = PERCENTILE(R, .25) = 0
  • QUARTILE(R, 2) = PERCENTILE(R, .5) = 4
  • QUARTILE(R, 3) = PERCENTILE(R, .75) = 5
  • QUARTILE(R, 4) = PERCENTILE(R, 1) = 7

Excel 2010/2013 Function: Excel 2010/2013 introduce an alternative version of the quartile function named QUARTILE.EXC. This function is defined so that QUARTILE.EXC(R, n) = PERCENTILE.EXC(R, n/4).

Excel 2010/2013 also provide the new function QUARTILE.INC which is equivalent to QUARTILE.

18 Responses to Ranking Functions in Excel

  1. Joanna says:

    What if you have a set of 10,000 responses — let’s say the answers are 0-100 and some people don’t answer, so there are blanks for some responses. I don’t want them included in determining the percentile, but when I do the calc, Excel is using the blanks as zero in the calculation, but not including them in the count for that column. Is there an easy way to pull the blank answers out of the calculation for the percentile calculation? (Specifically I’m looking for 25th, 50th, and 75th percentiles.)

    I’ve pretty much figured out how I can do this in a long, convoluted way, but before I do that, I thought I’d see if there was an easier way considering I have so many of these to look at.

    Thanks in advance.

    • Joanna says:

      Never mind – I had to prove it to myself by doing it the hard way that Excel isn’t including non-numerical or blanks as zeros. It really isn’t. 😉

  2. Salim says:

    Suppose we have a class of 35 who appeared in a test.
    1. If we want to calculate percentile, should we use formula which is the number of students below divided by total number of students multiply by 100 or we simply take highest number as 100 and calculate the rest on relative basis. 2. What should be the minimum total number to calculate percentile. What I mean is that if the total number is less, can we still calculate percentile. Is there any cutoff value?

    • Charles says:

      Salim,
      1. Either approach can be used, although the result won’t be the same. The referenced webpage explains how to calculate percentile using Excel.
      2. There is no minimum total number, although obviously with a very small sample the value won’t have much meaning.
      Charles

  3. Erik says:

    Hi,

    Thank you for this helpful page!

    I usually use PERCENTRANK since I think it’s a great function. The problem with this formula and also the same problem with RANK, LARGE, SMALL, etc is that the ranking gets equally distributed.

    Imagine I have range of elements consisting of {1,2,3,4,10,20} then by using PERCENTRANK I get {0%,20%,40%,60%,80%,100%}, but the problem is that I don’t see how “far” from the others each value is. I would have preferred getting something like {0%,5%,10%,15%,50%,100%} if you understand what I mean (note: these percentages are just examples).

    Is there any formula to help me get such a ranking?

    Thank you.

  4. Chuck Bennett says:

    I’m sorry if this question is answered above and I just didn’t recognize it…

    I would like to use the PERCENTRANK function to tell me the percent of data elements EQUAL TO and below c (not just below c).

    • Charles says:

      Chuck,

      In some sense PERCENTRANK already gives the percentile of data elements equal to or less than c. E.g. if range R contains the values 2, 5, 7, 8, 9, then PERCENTRANK(R,9) = 1 and PERCENTRANK(R,8) = .75. The problem comes in when R contains repeated values. If R contains the values 2, 5, 7, 8, 8, then PERCENTRANK(R,8) = .75 and PERCENTRANK(R,7) = .50.

      If you strictly want the percentage of values in R less than or equal to some value c, then you can use the formula =COUNTIF(R,”<="&c)/COUNT(R). Here you need to replace R by an actual range (e.g. A1:B5) and c by an actual number or reference to a number (e.g. 5 or C4 or C4/2). Note that I have just updated the referenced webpage with a clearer, but admittedly nor completely clear, version of the subject. Charles

  5. Nirosha says:

    Dear Chalers,
    I have 100 of sample and want to analysis factors affecting to information sharing patter and used five factors and asked to rank them from 1-5. Most important one rank as 1 and least important one as 5.
    can I use the above ranking function to list the most important to least important factor ? or do i need to calculate mean rank value for each factor and decide which factor is most important?

    • Charles says:

      Dear Nirosha,
      You can certainly use the ranking function to list the most important to least important fact, but I don’t have enough information to tell whether this is sufficient for your underlying needs.
      Charles

      • Nirosha says:

        Dear Charles,
        Thank you very much.let me explain bit more. I want to analysis farmers information sharing pattern using perception of farmer. I used 100 farmers and mentioned 5 factors. then asked them to rank these factors as most important factor affecting to information sharing pattern as 5 and least important one as 1.
        after my data collection, I have data with different value for each factor. then How I analysis/list most important to least important factor?
        taking mean rank value for each factor ?
        or is there any promising methods to analysis my data?

        • Charles says:

          Dear Nirosha,
          Thanks that is clearer for me. You can calculate the average rank for each factor, thereby ordering the factors in terms of their average rank. You don-t need to use the RANK or RANK.AVG function to do this when you only have 5 factors.
          If you assume that the 100 farmers are representative of the population of farmers, then you can also do some statistical analysis to see whether the rankings that you obtained are representative of the population. E.g. factor A and B may have scores of 4.5 and 4.7 respectively, thereby showing that at least for your population of 100 farmers factor B is ranked as more important. This may or may not be representative of the entire population. You can test this for example using one/way ANOVA (along with some follow-up tests). This is described in the Real Statistics website.
          Charles

  6. Gervais says:

    Thanks for this, it is very helpful but there is one more piece I would like to see. If I have a dataset of numbers, for example a list of prices within a certain market, and I want to find out what percentile a given known number, price in this example, is within the sample is there an easy way I can calculate this without having to do a table showing all the percentiles and then matching my number to the nearest one?

  7. bob says:

    I have a simple problem with a formula for percent.

    I have a ranking of U.S. states over a 3 yr period.

    I have three unique lists, i.e. 2006 ranking of states, 2008 ranking of states and finally 2015 ranking of states.

    Now, I’m attempting to get the percent increase or decrease of each state’s rank from year to year.

    I’m using a simple percent formula (NEW – OLD)/OLD

    so, say Alaska’s rank for 2006 was 10 then in 2008 it was 22 and in 2015 it’s 45.

    I have a conditional format that expresses the formula answer as a percentage using delta triangles and color coding.

    THe problem is that the percentage is treating the number normally (as if not in a ranking).

    THe difference is that in my ranking if the STATE rank goes up (it actually goes down the list of rank) – opposite of what I need.

    example: Alaska went from ranking #10 in 2006 to rank #22 in 2008. Excel sees this as ONLY A NUMBER THAT INCREASES. It is not seeing it as a number that went DOWN IN RANK.

    Do you have any idea how to get EXCEL to adjust?

    • Charles says:

      Bob,
      The Excel RANK function has format RANK(x,R1,order). If order = 0 (or is omitted) then the ranking is in decreasing order. If order = 1 then the ranking is in ascending order. It sounds like you can get the desired result by changing the value of the order argument in the formula.
      If this doesn’t do what you want then when you calculate a percentage p, use 1/p instead, which will change the order (unless p = 0).
      Charles

  8. David says:

    It’s all Greek to me. =.= Allthoug I am interested in this theme. I think I have to deal a little more closely and intensively to that. So far I am often on tis website to come closer to my destination, namely to understand excel: http://www.excel-aid.com/the-excelrank-eq-function.html

Leave a Reply

Your email address will not be published. Required fields are marked *