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), 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

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(SELECT([Revenue], 1, 4), 0.80)

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%.