Statistics How To

Excel PERCENTRANK function and Excel PERCENTILE function

Statistics Videos > How to use the Excel PERCENTILE function and Excel PERCENTRANK function

Watch the video or read the steps below:

Excel PERCENTILE function and Excel PERCENTRANK function: Overview

Excel PERCENTILE function and Excel PERCENTRANK function
Excel 2013 has 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 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 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 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.

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

Comments are now closed for this post. Need help or want to post a correction? Please post a comment on our Facebook page and I'll do my best to help!
Excel PERCENTRANK function and Excel PERCENTILE function was last modified: October 15th, 2017 by Andale