askvity

How to Find Percentiles in Excel?

Published in Excel Functions 3 mins read

To find percentiles in Excel, use the PERCENTILE.INC function (or PERCENTILE in older versions). This function calculates the k-th percentile for a set of data.

Using the PERCENTILE.INC Function

The basic syntax is:

=PERCENTILE.INC(array, k)

  • array: This is the range of cells containing the data set for which you want to find the percentile.
  • k: This is the percentile value you want to calculate, expressed as a decimal between 0 and 1 (inclusive). For example, 0.25 for the 25th percentile, 0.5 for the 50th percentile (median), and 0.75 for the 75th percentile.

Example

Let's say you have a list of test scores in cells A1:A10, and you want to find the 70th percentile. You would use the following formula:

=PERCENTILE.INC(A1:A10, 0.7)

This will return the value below which 70% of the scores in the range A1:A10 fall.

Considerations

  • PERCENTILE vs. PERCENTILE.INC vs. PERCENTILE.EXC: Excel has multiple percentile functions. PERCENTILE.INC includes the end points of the data set in its calculation (inclusive percentile). PERCENTILE.EXC excludes the end points (exclusive percentile). The older PERCENTILE function is equivalent to PERCENTILE.INC. It is recommended to use PERCENTILE.INC or PERCENTILE.EXC for clarity and to control whether you want to include or exclude the end points of your data.

  • Error Values: If the k value is outside the range of 0 to 1, or if the array is empty, the function will return a #NUM! error.

  • Data Type: The array should contain numerical data. If it contains text or empty cells, the function will ignore those values.

Step-by-Step Guide

  1. Enter your data: Input your data into a column or row in your Excel sheet.
  2. Select a cell: Choose a cell where you want to display the percentile value.
  3. Enter the formula: Type =PERCENTILE.INC(, then select the range of cells containing your data, followed by a comma, and then enter the desired percentile value (between 0 and 1), and finally close the parenthesis ).
  4. Press Enter: Press Enter to calculate and display the percentile.

Excel's percentile functions provide a simple way to understand the distribution of your data and identify values at specific percentile ranks.

Related Articles