SCOREATPERCENTILE

Overview

The SCOREATPERCENTILE function calculates the score (value) at a given percentile of the input data. This is useful for determining thresholds or cutoffs in a dataset, such as the value below which a certain percentage of the data falls. The calculation uses the specified interpolation method to determine the score when the percentile does not correspond exactly to a data point. For more details, see the scipy.stats.scoreatpercentile documentation .

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

Usage

To use the function in Excel:


=SCOREATPERCENTILE(data, percentile, [method])

data (2D list, required): Input data as a column or row vector of float values.
percentile (float, required): Percentile to compute (0-100).
method (str, optional, default=‘fraction’): Interpolation method (‘fraction’, ‘lower’, ‘higher’).

The function returns a single value (float): the score at the specified percentile, or an error message (string) if the input is invalid.

Examples

Example 1: Median (50th Percentile) of a Simple List

Inputs:

data	percentile	method
1	50	fraction
2
3
4
5

Excel formula:


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

Expected output:

Result
3.0

Example 2: 25th Percentile with ‘lower’ Method

Inputs:

data	percentile	method
10	25	lower
20
30
40
50

Excel formula:


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

Expected output:

Result
20.0

Example 3: 90th Percentile with ‘higher’ Method

Inputs:

data	percentile	method
5	90	higher
7
8
12
15

Excel formula:


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

Expected output:

Result
15.0

Example 4: 0th Percentile (Minimum Value)

Inputs:

data	percentile	method
3	0	fraction
6
9

Excel formula:


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

Expected output:

Result
3.0

This demonstrates the function’s ability to compute the minimum value at the 0th percentile.

Python Code


from scipy.stats import scoreatpercentile as scipy_scoreatpercentile
 
def scoreatpercentile(data, percentile, method='fraction'):
    """
    Calculates the score at the given percentile of the input data.
 
    Args:
        data: 2D list or scalar of float values. Input data.
        percentile: Scalar (0-100). Percentile to compute.
        method: Interpolation method ('fraction', 'lower', 'higher'). Default is 'fraction'.
 
    Returns:
        The score at the specified percentile (float), or an error message (str) if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Convert scalar to 2D list if needed
    if not isinstance(data, list):
        data = [[data]]
    # Flatten 2D list to 1D
    try:
        flat_data = [float(item) for row in data for item in (row if isinstance(row, list) else [row])]
    except Exception:
        return "Invalid input: data must be a 2D list or scalar of numbers."
    if len(flat_data) == 0:
        return "Invalid input: data must not be empty."
    try:
        per = float(percentile)
    except Exception:
        return "Invalid input: percentile must be a number."
    if not (0 <= per <= 100):
        return "Invalid input: percentile must be between 0 and 100."
    if method not in ('fraction', 'lower', 'higher'):
        return "Invalid input: method must be 'fraction', 'lower', or 'higher'."
    try:
        result = scipy_scoreatpercentile(flat_data, per, interpolation_method=method)
    except Exception as e:
        return f"scipy.stats.scoreatpercentile error: {e}"
    return result

Example Workbook

Link to Workbook