PERCENTILE Function in Excel: Explained

In this article, you will learn how to use the PERCENTILE formula in Excel.

‍

How does the PERCENTILE formula work in Excel?

The PERCENTILE function in Excel calculates the k-th percentile for a range of values. The k-th percentile is the value below which k percent of the data falls.

‍

When should you use the PERCENTILE function in Excel?

The PERCENTILE function in Excel is proper when you need to analyze a large set of numerical data and want to find a specific percentile value within that data set. This function can help you identify the value below which a certain percentage of the data falls. Here are some examples of when you might want to use the PERCENTILE function:

1. Analyzing profit margins by client: Suppose you have a large data set about revenue and profit by the client and want to know the margin that falls below the 90th percentile.
2. Calculating sales targets: If you want to set sales targets based on previous sales performance, you can use the PERCENTILE function to determine the sales level below a certain percentile, such as the 75th percentile.
3. Measuring employee performance: If you have a data set of employee performance metrics, you can use the PERCENTILE function to determine the performance level below which a certain percentage of employees fall, such as the 50th percentile.

‍

How to use the PERCENTILE formula in Excel

The formula for the PERCENTILE function is as follows:

=PERCENTILE(array,k)

where "array" is the range of cells containing the dataset, and "k" is the percentile value you want to calculate, which must be between 0 and 1. So, if you want to get the number corresponding to the 50th percentile, you should substitute k with 0.5 in the formula.

Note 1: If you insert a non-numeric value in the “k” argument, the function returns #VALUE! error.

Note 2: If you insert a number below 0 (exclusive) or above 1 (again, exclusive), the formula gives you #NUM! error.

Note 3: If k is not a multiple of 1/(n-1), where n is the total number of items selected, the PERCENTILE function interpolates to decide the number at the k-th percentile. (You can see this happens in the example below)

‍

For example, suppose you have a client list containing revenue, profit, and profit margin and want to know what margin corresponds to a particular percentile given the data set.

1. Insert the PERCENTILE formula: =PERCENTILE(
2. Select the range containing the data you want to analyze for “array”
3. Enter the “k” argument by cell reference or manually

The following picture shows what the 90th, 70th, 50th, 20th, and 10th percentile look like with the sample dataset. We recommend using a cell reference for the “k” argument, as it allows you to change the argument quickly and easily.

‍

What is the difference between percentage and percentile?

Percentage and percentile are two terms often used in statistical analysis, but they have different meanings. A percentage is a way of expressing a number as a fraction of 100. It is often used to represent proportions, rates, and percentages of change. For example, if you have 80 correct answers out of 100 questions on a test, you have scored 80%, which means you got 80% of the total possible score. On the other hand, a percentile is a statistical measure that represents the percentage of data values that fall below a specific value in a data set. For example, if your score on a test is at the 80th percentile, it means that 80% of the test-takers scored below your score. The PERCENTILE formula helps you find a number positioned at the k-th percentile based on your input data.

‍

Analyze your live financial data in a snap in Google Sheets

Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly. 

To learn more about LiveFlow, book a demo.

‍

You can learn about other Excel and Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides