Statistics How To

Excel PERCENTRANK function and Excel PERCENTILE function

Excel for Statistics > 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!

------------------------------------------------------------------------------

If you prefer an online interactive environment to learn R and statistics, this free R Tutorial by Datacamp is a great way to get started. If you're are somewhat comfortable with R and are interested in going deeper into Statistics, try this Statistics with R track.

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: November 5th, 2017 by Stephanie Glen