How to Calculate a Percentile Using Excel

Written by pete collins
  • Share
  • Tweet
  • Share
  • Email

Percentiles are a useful way of measuring or manipulating the spread of a dataset. The percentile is the value below which that percentage of the dataset is found. For example, the 20th percentile in a dataset is the value below which 20% of the dataset resides. Thus, the 50th percentile is the value "in the middle," also known as the median. Excel allows you to calculate percentiles using the "Percentile" function.

Skill level:
Moderately Easy

Other People Are Reading

Instructions

  1. 1

    Enter your dataset in a continuous column in Excel. For this example, assume that the data is entered in cells A1 to A100.

  2. 2

    Determine what percentile you want to calculate, and convert it to a number between zero and one. For example, the 20th percentile is 0.2, the 55th percentile is 0.55, the 100th percentile is 1, and so on. This example will use the 40th percentile (0.4).

  3. 3

    Enter the following formula in the cell where you want the percentile result to show:

    =PERCENTILE(A1:A100,0.4).

    This tells Excel to calculate the 40th percentile of the data in cells A1 to A100.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.