SCOREATPERCENTILE

Overview

The SCOREATPERCENTILE function calculates the value (score) at a specified percentile of a dataset. A percentile indicates the value below which a given percentage of observations fall; for example, the 50th percentile is the median, meaning half the data points are below this value. This function is commonly used in statistical analysis to understand data distribution, identify outliers, and establish benchmark thresholds.

This implementation uses the scipy.stats.scoreatpercentile function from SciPy, a fundamental library for scientific computing in Python. The source code is available on GitHub.

When the desired percentile falls exactly on a data point, that value is returned directly. When it falls between two data points, the function uses an interpolation method to estimate the score. Three interpolation options are available:

• Fraction (default): Linear interpolation between adjacent data points i and j, calculated as:

\text{score} = i + (j - i) \times f

where f is the fractional part of the index position between i and j.

• Lower: Returns the smaller of the two adjacent values (i).
• Higher: Returns the larger of the two adjacent values (j).

The percentile parameter accepts values from 0 to 100, where 0 returns the minimum value, 100 returns the maximum value, and 50 returns the median. This function is the inverse of PERCENTILEOFSCORE, which calculates what percentile a given score represents within a dataset.

This example function is provided as-is without any representation of accuracy.

Excel Usage

=SCOREATPERCENTILE(data, percentile, interp_method)

• data (list[list], required): 2D array of numeric values representing the input data.
• percentile (float, required): Percentile to compute, ranging from 0 to 100.
• interp_method (str, optional, default: “fraction”): Interpolation method used when percentile lies between data points.

Returns (list[list]): 2D list containing the score at the specified percentile, or error message string if input is invalid.

Examples

Example 1: Median (50th percentile) using fraction method

Inputs:

data	percentile	interp_method
1	50	fraction
2
3
4
5

Excel formula:

=SCOREATPERCENTILE({1;2;3;4;5}, 50, "fraction")

Expected output:

Result
3

Example 2: 25th percentile using lower method

Inputs:

data	percentile	interp_method
10	25	lower
20
30
40
50

Excel formula:

=SCOREATPERCENTILE({10;20;30;40;50}, 25, "lower")

Expected output:

Result
20

Example 3: 90th percentile using higher method

Inputs:

data	percentile	interp_method
5	90	higher
7
8
12
15

Excel formula:

=SCOREATPERCENTILE({5;7;8;12;15}, 90, "higher")

Expected output:

Result
15

Example 4: Minimum value (0th percentile)

Inputs:

data	percentile	interp_method
3	0	fraction
6
9

Excel formula:

=SCOREATPERCENTILE({3;6;9}, 0, "fraction")

Expected output:

Result
3

Python Code

from scipy.stats import scoreatpercentile as scipy_scoreatpercentile

def scoreatpercentile(data, percentile, interp_method='fraction'):
    """
    Calculates the score at the given percentile of the input data.

    See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.stats.scoreatpercentile.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): 2D array of numeric values representing the input data.
        percentile (float): Percentile to compute, ranging from 0 to 100.
        interp_method (str, optional): Interpolation method used when percentile lies between data points. Valid options: Fraction, Lower, Higher. Default is 'fraction'.

    Returns:
        list[list]: 2D list containing the score at the specified percentile, or error message string if input is invalid.
    """
    def to2d(x):
        return [[x]] if not isinstance(x, list) else x

    data = to2d(data)

    try:
        flat_data = [float(item) for row in data for item in (row if isinstance(row, list) else [row])]
    except (TypeError, ValueError):
        return "Invalid input: data must be a 2D list of numbers."

    if len(flat_data) == 0:
        return "Invalid input: data must not be empty."

    try:
        per = float(percentile)
    except (TypeError, ValueError):
        return "Invalid input: percentile must be a number."

    if not (0 <= per <= 100):
        return "Invalid input: percentile must be between 0 and 100."

    valid_methods = ('fraction', 'lower', 'higher')
    if interp_method not in valid_methods:
        return f"Invalid input: interp_method must be one of {valid_methods}."

    try:
        result = scipy_scoreatpercentile(flat_data, per, interpolation_method=interp_method)
    except Exception as e:
        return f"Calculation error: {e}"

    return [[float(result)]]

Online Calculator