Excel PERCENTRANK Function, PERCENTILE & RANK

Excel for Statistics > How to use the Excel PERCENTILE function, PERCENTRANK function and RANK function

Contents:
PERCENTILE / PERCENTRANK
RANK

Excel PERCENTILE function and Excel PERCENTRANK function: Overview

Watch the video or read the steps below:

Update 09/02/2016: The video is for Excel 2013; The steps for Excel 2016 are exactly the same.

Excel PERCENTILE function and Excel PERCENTRANK function
Excel 2013 & 2016 have four options for finding a percentile. Which one you use depends on what you want to do. In previous editions of Excel there were two percentile functions: PERCENTRANK and PERCENTILE. The Excel PERCENTRANK function gave you a percentile for a particular rank, while PERCENTILE returned the particular score from a list. In Excel 2013/2018 there are now four options:

  • PERCENTILE.INC Finds a score given a percentile. For example, you can ask Excel to find what score is in the 50th percentile. The “INC” part of the function means to include 0% and 100% in the tallies.
  • PERCENTILE.EXC is exactly the same as PERCENTILE.INC with one difference: 0% and 100% are excluded. For example, it will calculate the 99th percentile but not the 100th percentile.
  • PERCENTRANK.INC will return the ranking for a particular data item. for example, if you have a list of scores for a test, the function will give you a percentage ranking (i.e. 99th percent) for a particular data item. In other words, if you have a score of, say 50 on a test, you can find out what your ranking is. It included 0% and 100%.
  • PERCENTRANK.EXC is exactly the same as PERCENTRANK.INC but it excludes 0% and 100%

Excel PERCENTILE function: Steps

Step 1: Click an empty cell.

Step 2: Type =PERCENT and then double-click the function you need from the dropdown list (for example, double click PERCENTRANK.INC

Step 3: Click the “fx” (Insert Function) button on the toolbar. This opens the function’s dialog box.

Step 4: Type in the requested items and then click “OK.”:

  1. Array: The cell location for your data.
  2. k: The percentage that you want to find the score for. Enter as a decimal (0 to 1 for PERCENTILE.INC)

Excel PERCENTRANK function: Steps

Step 1: Click an empty cell.

Step 2: Type =PERCENT and then double-click the function you need from the dropdown list (for example, double click PERCENTRANK.INC

Step 3: Click the “fx” (Insert Function) button on the toolbar. This opens the function’s dialog box.

Step 4: Type in the requested items and then click “OK.”:

  1. Array: the list of data (i.e. a set of scores from a test)
  2. x: the data item you want to find the percentage rank for
  3. Significance: The number of significant digits (decimal places) you want returned in the answer.

Excel RANK function (Excel 2013-2018)

Watch the video or read the steps below:

Excel RANK function: Overview

Excel gives you plenty of choices for finding rank:

  • The Data Analysis Toolpak.
  • The RANK.EQ function (this is equivalent to the old RANK function, which is now obsolete).
  • The RANK.AVG function.

The difference between RANK.EQ and RANK.AVG is subtle, and it concerns duplicate ranks. For example, let’s say you had four students with GPAs of 4.0, 3.5, 3.5, and 3.0. RANK.EQ would return the following ranks:
4.0 – 1
3.5 – 2
3.5 – 2
3.0 – 4
Note that the third rank is skipped and the 3.5s both rank at 2. RANK.AVG averages the ranks — which are actually 2 and 3 in this case and would return the average of the 2 ranks (2 and 3):
4.0 – 1
3.5 – 2.5
3.5 – 2.5
3.0 – 4

The inputs for the two rank functions are exactly the same.

How to Use the Excel RANK function: RANK.EQ and RANK.AVG

Step 1: Type your data into a single column.

Step 2: Click the cell immediately to the right of the first data point you want to find a rank for. For example, if you entered data into cells B1:B5, click cell C1.

Step 3: Type “=RANK(“. Excel will give you a dropbox with three options. Double click the option you want (RANK.EQ or RANK.AVG). If you are unfamiliar with entering formulas, click the insert function button (fx) on the toolbar to bring up a formula box where you get detailed hints about how to complete the formula. If you bring up the formula box, skip Step 4.

Three option drop-down box for the Excel rank function.
Three option drop-down box for the Excel rank function.

Step 4: Fill in the rest of the formula, which is =RANK.EQ(data item, data set, descending/ascending order). For example, =RANK.EQ(B2,$B$2:$B$11,0) tells Excel that you want to rank the item in B2 against the data set located in cells B2:B11.

Step 5: Press “Enter.”

How to Use the Excel RANK function: Data Analysis

Step 1: Click the “Data” tab and then click “Data Analysis.”

Step 2: Click “Rank and Percentile” and then click “OK.”

Step 3: Click in the Input Range box and then type the location of your data set (i.e. B1:B99)

Step 4: Click either the columns or rows radio button, depending on how your data is formatted. The most common format is columns. Check the “Labels in first row” box if you have column headers.

Step 5: Select an output range. For example, select an area immediately to the right of your input data.

Step 6: Click “OK.”

Check out our YouTube channel for more Excel tips and help!


Comments? Need to post a correction? Please Contact Us.