GMEAN

This function calculates the geometric mean of positive numeric values from a 2D input range.

For values x_1, x_2, \ldots, x_n with x_i > 0, the geometric mean is:

G = \left(\prod_{i=1}^{n} x_i\right)^{1/n}

Non-numeric values are ignored, and the function returns an error if no positive numeric values are available.

Excel Usage

=GMEAN(data)

data (list[list], required): 2D array of positive numeric values. Non-numeric values are ignored.

Returns (float): Geometric mean of the input data, or error message (str) if input is invalid.

Example 1: Geometric mean of 2x2 matrix

Inputs:

data
1	2
3	4

Excel formula:

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

Expected output:

2.21336

Example 2: Non-numeric values are ignored

Inputs:

data
2	a
8	4

Excel formula:

=GMEAN({2,"a";8,4})

Expected output:

4

Example 3: Single row of values

Inputs:

data
5	10

Excel formula:

=GMEAN({5,10})

Expected output:

7.07107

Example 4: Single column with multiple rows

Inputs:

data
2
8
32

Excel formula:

=GMEAN({2;8;32})

Expected output:

8

Python Code

from scipy.stats import gmean as scipy_gmean

def gmean(data):
    """
    Compute the geometric mean of the input data, flattening the input and ignoring non-numeric values.

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

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

    Args:
        data (list[list]): 2D array of positive numeric values. Non-numeric values are ignored.

    Returns:
        float: Geometric mean of the input data, or error message (str) if input is invalid.
    """
    def to2d(x):
        return [[x]] if not isinstance(x, list) else x

    try:
      data = to2d(data)

      if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
        return "Error: Invalid input: data must be a 2D list."

      flat = []
      for row in data:
        for x in row:
          try:
            val = float(x)
            if val > 0:
              flat.append(val)
          except (TypeError, ValueError):
            continue

      if not flat:
        return "Error: Input must contain at least one positive number."

      result = scipy_gmean(flat)
      return float(result)
    except Exception as e:
      return f"Error: {str(e)}"

Online Calculator

data *

2D array of positive numeric values. Non-numeric values are ignored.