PERCENTILE Function in Microsoft Excel

Part 1: Introduction

Definition

The PERCENTILE function in Excel is a statistical function that returns the k-th percentile of values in a range. A percentile is a measure used in statistics indicating the value below which a given percentage of observations in a group of words falls.

Purpose

This function is often used to establish a threshold of acceptance. For instance, in recruitment, you may consider only candidates who score above the 90th percentile.

Syntax & Arguments

The syntax for the PERCENTILE function is as follows:

Arguments:

• array: This is a required argument. It represents the array or range of data that defines relative standing.
• k: This is also a required argument. It represents the percentile value in the range 0..1, inclusive.

Return Value

The PERCENTILE function returns the k-th percentile of values in a given range.

Remarks

• If k is non-numeric, PERCENTILE returns the #VALUE! Error value.
• If k is less than 0 or greater than 1, PERCENTILE returns the #NUM! Error value.
• If k is not a multiple of 1/(n – 1), PERCENTILE interpolates to determine the value at the k-th percentile.

Part 2: Examples

Let’s look at examples of how the PERCENTILE function can be used in a business context.

Example 1

Purpose: To find the 90th percentile of sales data.

Data tables and formulas:

Explanation: We have sales data for three different IDs in this example. We use the PERCENTILE function to find the 90th percentile of the sales data. The formula =PERCENTILE(B2:B4, 0.9) is entered in cell C5. The 650 result indicates that 90% of the sales data is below this value.

Example 2

Purpose: To find customer satisfaction scores’ 50th percentile (median).

Data tables and formulas:

Explanation: Here, we have customer satisfaction scores for three customers. We use the PERCENTILE function to find the scores’ median (50th percentile). The formula =PERCENTILE(B2:B4, 0.5) is entered in cell C5. The result, 8, indicates that 50% of the scores are below this value.

Example 3

Purpose: To find the 25th percentile of employee performance scores.

Data tables and formulas:

Explanation: In this example, we have performance scores for three employees. We use the PERCENTILE function to find the 25th percentile of the scores. The formula =PERCENTILE(B2:B4, 0.25) is entered in cell C5. The 87.5 result indicates that 25% of the scores are below this value.

Example 4

Purpose: To find the 75th percentile of product ratings.

Data tables and formulas:

Explanation: Here, we have ratings for three products. We use the PERCENTILE function to find the 75th percentile of the ratings. The formula =PERCENTILE(B2:B4, 0.75) is entered in cell C5. The result, 4.6, indicates that 75% of the ratings are below this value.

Example 5

Purpose: To find the 80th percentile of website load times.

Data tables and formulas:

Explanation: In this example, we have load times for three websites. We use the PERCENTILE function to find the 80th percentile of the load times. The formula =PERCENTILE(B2:B4, 0.8) is entered in cell C5. The result, 2.9, indicates that 80% of the load times are below this value.

Example 6: PERCENTILE with IF

Purpose: To find the 90th percentile of sales data only for IDs with sales greater than 500.

Data tables and formulas:

Explanation: In this example, we use the IF function within the PERCENTILE function to consider only sales more significant than 500. The formula =PERCENTILE(IF(B2:B4>500, B2:B4), 0.9) is entered in cell C5. The hypothetical result, 690, indicates that 90% of the sales data for IDs with sales greater than 500 is below this value.

Example 7: PERCENTILE with SUM

Purpose: To find the sum of customer satisfaction scores’ 25th and 75th percentiles.

Data tables and formulas:

Explanation: Here, we use the SUM function with the PERCENTILE function to find the sum of the 25th and 75th percentiles of the customer satisfaction scores. The formula =SUM(PERCENTILE(B2:B4, 0.25), PERCENTILE(B2:B4, 0.75)) is entered in cell C5. The hypothetical result, 16.5, is the sum of the 25th and 75th percentiles of the scores.

Example 8: PERCENTILE with VLOOKUP

Purpose: Find the 50th percentile (median) of employee performance scores and then look up the corresponding employee ID.

Data tables and formulas:

Explanation: In this example, we use the VLOOKUP function with the PERCENTILE function to find the employee ID corresponding to the median performance score. The formula =VLOOKUP(PERCENTILE(B2:B4, 0.5), A2:B4, 1, FALSE) is entered in cell C5. The hypothetical result, 2, is the employee ID related to the median performance score.

Example 9: PERCENTILE with AVERAGE

Purpose: To find the average of product ratings’ 30th and 70th percentiles.

Data tables and formulas:

Explanation: Here, we use the AVERAGE function with the PERCENTILE function to find the average of the 30th and 70th percentiles of the product ratings. The formula =AVERAGE(PERCENTILE(B2:B4, 0.3), PERCENTILE(B2:B4, 0.7)) is entered in cell C5. The hypothetical result, 4.5, is the average of the 30th and 70th percentiles of the ratings.

Part 3: Tips and Tricks

1. Remember that the k value should be between 0 and 1. If it’s not, Excel will return an error.
2. The PERCENTILE function has been replaced with two new functions, PERCENTILE.INC and PERCENTILE.EXC, which provides improved accuracy. Consider using these new functions for future projects.
3. If your data set contains outliers, percentiles can give you a better understanding of your data’s distribution compared to the mean or median.
4. You can create more complex formulas and analyses using the PERCENTILE function and other functions. For example, you could use it with the IF function to categorize data based on percentile thresholds.
5. Always check your data range to ensure it’s correct before running the PERCENTILE function. A small error in the field could lead to incorrect results.