PERCENTILEEXC

Calculates the exclusive percentile of a dataset to find the value below which a given percentage of data falls.

The PERCENTILEEXC function returns the k-th percentile of a dataset using the exclusive method, requiring k to be between 0 and 1 (exclusive) and excluding the minimum and maximum values from the calculation.

Syntax

PERCENTILEEXC(array, k)

Arguments

The PERCENTILEEXC function syntax has the following arguments:

• array: The range or list of numeric values for which you want to calculate the percentile. Required.

• k: The percentile value to return, expressed as a decimal between 0 and 1 (exclusive). Required.

  • 0.25 - 25th percentile

  • 0.5 - Median (50th percentile)

  • 0.75 - 75th percentile

• k must be greater than 0 and less than 1.

• Unlike PERCENTILEINC, this function excludes the minimum (0) and maximum (1) values from the calculation.

• If a value less than or equal to 0, or greater than or equal to 1 is provided, the PERCENTILEEXC function returns a #ERR error.

Example

PERCENTILEEXC(10,20,30,40,50,60,70,80, 0.25)
// Returns 22.5
PERCENTILEEXC(10,20,30,40,50,60,70,80, 0.5)
// Returns 45
PERCENTILEEXC(10,20,30,40,50,60,70,80, 0.75)
// Returns 67.5

You can use the PERCENTILEEXC function to determine the relative standing of values within a dataset by calculating exclusive percentiles for metrics such as sales, revenue, or performance scores, especially when working with sample data.

PERCENTILEEXC function

We used the SELECT function to select a range of columns from 1 to 4 for the 'Revenue' measure (2022 Revenue - 2025 Revenue)

Excel Equivalent

PERCENTILEEXC

FAQs

Q1. How is PERCENTILEEXC calculated?

PERCENTILEEXC is calculated by determining the k-th percentile of a dataset using the exclusive method, which excludes the minimum and maximum values from the calculation.

Where:

• k = desired percentile (0<k<1)

• n = number of values

If the calculated position is not a whole number, the result is obtained by interpolating between the surrounding values. It returns the value below which a specified percentage of data falls, excluding both the minimum and maximum values.

Q2. What does a percentile represent?

A percentile shows how a value compares to other values in a dataset.

For example, consider student exam scores. If a student’s score is at the 90th percentile:

• 90% of the students scored below this value.

• 10% of the students scored above this value.

This means the student performed better than most of the class and is among the top 10%.