How To Use PERCENTILE function Excel Guide

What is PERCENTILE function in Excel?

The PERCENTILE function in Excel returns the k-th percentile of a range of data. The syntax is =PERCENTILE(array, k) where the array is the data range, and k is the percentile value.

Summary

Function Definition
About Function
Function Syntax
How to use Function in your workbook
Frequently Asked Questions
More similar formulas

PERCENTILE Function Description

The PERCENTILE function in Excel is a statistical function used to calculate the rank of a given value in a data set.

It’s useful for determining the percentile rank of a given value, meaning what percentage of values in the data set is equal to or less than the given value.

The function takes two arguments, both of which are required.

The first argument is the array of values or the data set;
The second argument is the percentile value you want to calculate the rank of. This value must be between 0 and 1 and specifies the fraction of the data set that is equal to or less than the given value.

For example, if the percentile value you specify is 0.5, the function will return the 50th percentile, or the median, of the data, set.

The median is the middle value in a sorted list of values. If the percentile value you specify is 0.9, the function will return the 90th percentile or the value that is greater than or equal to 90% of the values in the data set.

The PERCENTILE formula is useful for interpreting the relative performance of a value in a data set. It can be used to determine the relative ranking of test scores, employee performance,investment returns, or any other type of data set.

Struggling with your Excel formulas?
Looking for a faster and easier way to write Excel formulas? Try AI Excel Formula Generator and turn your text into formulas with just a few clicks.

Try for free

Syntax

=PERCENTILE(array,k)

where:

array = the range of cells that contain the values

k = the percentile value (a number between 0 and 1)

How to use PERCENTILE function in your workbook:

Percentile function MS Excel - Excel Formula Cheat Sheet

Open the Excel worksheet containing the data you want to use to calculate the percentile.
Select the cell where you want the results of the percentile calculation to appear.
Type the following formula into the selected cell: =PERCENTILE(data, k)
Replace the “data” part of the formula with the range of cells containing the data you want to use for the percentile calculation.
Replace the “k” part of the formula with the percentile value you want to calculate. This should be expressed as a decimal from 0 to 1. For example, 0.7 for the 70th percentile.
Press “Enter” to calculate the result.

Back to Excel Formula Cheat Sheet

Frequently Asked Questions

What is a percentile in statistics?

A percentile is a value below which a certain percentage of the data falls. For example, the 25th percentile is the value below which 25% of the data falls.

Can I use the PERCENTILE formula with a range of cells that contain errors or text?

No, the PERCENTILE formula only works with numerical values. If your range contains errors or text, you may need to use other functions or formulas to clean up the data before using the function.

What happens if there are duplicate values in my data range?

The PERCENTILE formula treats duplicate values as separate values when calculating percentiles. For example, if you have two values of 10 in your data range and you calculate the 50th percentile, the function will return the value that is below 50% of the values, which could be either of the 10s or a value between them.