RELFREQ

Overview

The RELFREQ function returns a relative frequency histogram for an input array. It divides the data into bins and counts the number of observations in each bin relative to the total number of observations. This is useful for visualizing the distribution of data and comparing frequencies across bins. For more details, see the scipy.stats.relfreq documentation .

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

Usage

To use the function in Excel:


=RELFREQ(data, [numbins], [lowerlimit], [upperlimit])

data (2D list, required): Input data as a column or row vector of float values.
numbins (int, optional, default=10): Number of bins to use for the histogram.
lowerlimit (float, optional): Lower bound for the histogram range.
upperlimit (float, optional): Upper bound for the histogram range.

The function returns a 2D list of relative frequencies for each bin, or an error message (string) if the input is invalid.

Examples

Example 1: Simple List with 4 Bins

Inputs:

data	numbins	lowerlimit	upperlimit
2	4
4
1
2
3
2

Excel formula:


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

Expected output:

Result
0.16666667
0.5
0.16666667
0.16666667

Example 2: 5 Bins, Custom Range

Inputs:

data	numbins	lowerlimit	upperlimit
1	5	1	5
2
3
4
5

Excel formula:


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

Expected output:

Result
0.2
0.2
0.2
0.2
0.2

Example 3: 3 Bins, Default Range

Inputs:

data	numbins	lowerlimit	upperlimit
10	3
20
30
40
50

Excel formula:


=RELFREQ({10;20;30;40;50}, 3)

Expected output:

Result
0.2
0.4
0.4

Example 4: 2 Bins, Custom Range

Inputs:

data	numbins	lowerlimit	upperlimit
5	2	5	15
10
15

Excel formula:


=RELFREQ({5;10;15}, 2, 5, 15)

Expected output:

Result
0.33333333
0.66666667

Python Code


from scipy.stats import relfreq as scipy_relfreq
 
def relfreq(data, numbins=10, lowerlimit=None, upperlimit=None):
    """
    Returns the relative frequency histogram for the input data.
 
    Args:
        data: 2D list or scalar of float values. Input data to analyze.
        numbins: Number of bins to use for the histogram (default 10).
        lowerlimit: Lower bound for the histogram range (optional).
        upperlimit: Upper bound for the histogram range (optional).
 
    Returns:
        2D list of relative frequencies for each bin, 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:
        nbins = int(numbins)
    except Exception:
        return "Invalid input: numbins must be an integer."
    if nbins < 1:
        return "Invalid input: numbins must be >= 1."
    # Prepare limits
    limits = None
    if lowerlimit is not None and upperlimit is not None:
        try:
            limits = (float(lowerlimit), float(upperlimit))
        except Exception:
            return "Invalid input: lowerlimit and upperlimit must be numbers."
    try:
        if limits:
            res = scipy_relfreq(flat_data, numbins=nbins, defaultreallimits=limits)
        else:
            res = scipy_relfreq(flat_data, numbins=nbins)
        freq = res.frequency
    except Exception as e:
        return f"scipy.stats.relfreq error: {e}"
    # Return as 2D list (column vector)
    return [[float(x)] for x in freq]

Example Workbook

Link to Workbook