PERCENTRANKEXC

Calculates the exclusive percentage rank of a value within a dataset to determine its relative position among other values.

The PERCENTRANKEXC function returns the percentage rank of a value within a dataset using the exclusive method. It indicates the relative position of a value compared to other values and excludes the minimum and maximum values in the calculation.

Syntax

PERCENTRANKEXC(array, x, [significance])

Arguments

The PERCENTRANKEXC function syntax has the following arguments:

• array: The range or list of numeric values.

• x: The value for which you want to calculate the percentage rank.

• significance (optional): The number of significant digits for the result.

The result ranges from greater than 0 and less than 1 (exclusive). It does not return 0 or 1 because the minimum and maximum values are excluded from the calculation.

Example

PERCENTRANKEXC(10,20,30,40,50, 30)
// Returns 0.5
PERCENTRANKEXC(10,20,30,40,50, 20)
// Returns 0.25
PERCENTRANKEXC(10,20,30,40,50, 40)
// Returns 0.75

You can use the PERCENTRANKEXC function to determine the relative standing of a value within a dataset when excluding the minimum and maximum values, especially when working with sample data.

• If the value being ranked is less than the minimum in the specified array, the PERCENTRANKEXC function returns 0.

• If the value being ranked is greater than the maximum in the specified array, the function returns an #ERR error.

PERCENTRANKEXC 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

PERCENTRANKEXC

FAQs

Q1. How is PERCENTRANKEXC calculated?

PERCENTRANKEXC is calculated by determining the relative position of a value within a sorted dataset using the exclusive method, which excludes the minimum and maximum values in the calculation.

PERCENTRANKEXC = r / (n+1)​

Where:

• r = rank position of the value in the sorted dataset

• n = number of values in the dataset

If the value does not exactly match an existing value, the result is obtained by interpolating between the surrounding values.

It returns a value between 0 and 1 (exclusive) representing the relative standing of the value in the dataset.