MODE

Overview

The MODE function returns the modal (most common) value in the passed array. This function flattens the input, ignores non-numeric values, and always returns a single mode, unlike scipy.stats.mode which can return multiple modes. Excel’s MODE.SNGL and MODE.MULT can return multiple modes; this function returns only the first. If there are multiple values with the same highest frequency, the smallest value is returned. This function is useful for quickly identifying the most frequent value in a dataset.

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

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

Usage

To use the function in Excel:


=MODE(data)

data (2D list, required): The input data as a 2D list (table or range). Non-numeric values are ignored. Must contain at least two numeric elements.

The function returns a single value (float or int): the most common value in the data, or an error message (string) if the input is invalid or no numeric values are present.

Examples

Example 1: Simple Mode

Inputs:

data
1	2
2	3
2	4

Excel formula:


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

Expected output:

Result
2

Example 2: Multiple Modes (Returns Smallest)

Inputs:

data
1	2
2	1
3	3

Excel formula:


=MODE({1,2;2,1;3,3})

Expected output:

Result
1

Example 3: Non-numeric Values Ignored

Inputs:

data
1	a
2	2
2	b

Excel formula:


=MODE({1,"a";2,2;2,"b"})

Expected output:

Result
2

Example 4: All Unique Values (No Mode)

Inputs:

data
1	2
3	4
5	6

Excel formula:


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

Expected output:

Result
”No mode found”

Python Code


from scipy.stats import mode as scipy_mode
 
def mode(data):
    """
    Returns the modal (most common) value in the passed array. Flattens the input, ignores non-numeric values, and always returns a single mode (the smallest if multiple). If no mode is found, returns "No mode found".
    
    Args:
        data: 2D list of values (float, int, or str). Non-numeric values are ignored.
    
    Returns:
        float or int: The most common value in the data, or a string error message if invalid.
    
    This example function is provided as-is without any representation of accuracy.
    """
    # If input is not a list of lists, return error
    if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
        return "Two or more data elements are needed"
    # Flatten and filter numeric values
    flat = []
    for row in data:
        for val in row:
            try:
                v = float(val)
                if v.is_integer():
                    v = int(v)
                flat.append(v)
            except (ValueError, TypeError):
                continue
    if len(flat) < 2:
        return "Two or more data elements are needed"
    # Use scipy.stats.mode
    res = scipy_mode(flat, keepdims=False, axis=None, nan_policy='omit')
    if res.count < 2:
        return "No mode found"
    # Return as Python scalar, not numpy type
    return res.mode.item() if hasattr(res.mode, 'item') else res.mode

Live Demo

Example Workbook

Link to Workbook