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.
PERCENTRANKEXC(array, x, [significance])The PERCENTRANKEXC function syntax has the following arguments:
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.
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.
PERCENTRANKEXC
We used the SELECT function to select a range of columns from 1 to 4 for the 'Revenue' measure (2022 Revenue - 2025 Revenue)
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:
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.
