PERCENTILEOFSCORE

Overview

The PERCENTILEOFSCORE function computes the percentile rank of a score relative to a list of scores. This is useful for determining the relative standing of a value within a dataset, such as ranking test scores or other measurements. The calculation supports different ranking methods for handling ties:

rank: Average percentage ranking of the score. In case of multiple matches, averages the percentage rankings of all matching scores.
weak: Percentile is the percentage of values less than or equal to the score.
strict: Percentile is the percentage of values strictly less than the score.
mean: The average of the weak and strict scores.

For more details, see the scipy.stats.percentileofscore documentation .

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

Usage

To use the function in Excel:


=PERCENTILEOFSCORE(data, score, [method])

data (2D list or scalar, required): Reference data. Each value should be a float. If a scalar is provided, it is treated as a single-element list.
score (float, required): The value to rank within the data.
method (str, optional, default=‘rank’): Ranking method. One of ‘rank’, ‘weak’, ‘strict’, or ‘mean’.

The function returns a single value (float): the percentile rank (0-100) of the score within the data, or an error message (string) if the input is invalid.

Examples

Example 1: Basic Percentile Rank

Inputs:

data	score	method
1	3	rank
2
3
4

Excel formula:


=PERCENTILEOFSCORE({1;2;3;4}, 3)

Expected output:

Result (%)
75.0

Example 2: Tied Scores (mean method)

Inputs:

data	score	method
1	3	mean
2
3
3
4

Excel formula:


=PERCENTILEOFSCORE({1;2;3;3;4}, 3, "mean")

Expected output:

Result (%)
60.0

Example 3: Strict Method

Inputs:

data	score	method
1	3	strict
2
3
3
4

Excel formula:


=PERCENTILEOFSCORE({1;2;3;3;4}, 3, "strict")

Expected output:

Result (%)
40.0

Example 4: Weak Method

Inputs:

data	score	method
1	3	weak
2
3
3
4

Excel formula:


=PERCENTILEOFSCORE({1;2;3;3;4}, 3, "weak")

Expected output:

Result (%)
80.0

This demonstrates the effect of different methods on the percentile rank calculation.

Python Code


from scipy.stats import percentileofscore as scipy_percentileofscore
 
def percentileofscore(data, score, method='rank'):
    """
    Computes the percentile rank of a score relative to the input data.
 
    Args:
        data: 2D list or scalar of float values. Reference data.
        score: Scalar value to rank.
        method: Ranking method ('rank', 'weak', 'strict', 'mean'). Default is 'rank'.
 
    Returns:
        Percentile rank (float, 0-100), or an error message (str) if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Convert data to 1D list
    if isinstance(data, (int, float)):
        arr = [float(data)]
    elif isinstance(data, list):
        # Flatten 2D list to 1D
        arr = []
        for row in data:
            if isinstance(row, list):
                arr.extend(row)
            else:
                arr.append(row)
        try:
            arr = [float(x) for x in arr]
        except Exception:
            return "Invalid input: data must be numeric."
    else:
        return "Invalid input: data must be a 2D list or scalar."
    if len(arr) == 0:
        return "Invalid input: data must not be empty."
    try:
        s = float(score)
    except Exception:
        return "Invalid input: score must be a number."
    if method not in ('rank', 'weak', 'strict', 'mean'):
        return "Invalid input: method must be one of 'rank', 'weak', 'strict', 'mean'."
    try:
        result = scipy_percentileofscore(arr, s, kind=method)
    except Exception as e:
        return f"scipy.stats.percentileofscore error: {e}"
    # Disallow nan/inf
    if result is None or (isinstance(result, float) and (result != result or abs(result) == float('inf'))):
        return "Invalid result: output is nan or inf."
    return result

Example Workbook

Link to Workbook