GMEAN
Overview
The GMEAN function computes the geometric mean of the input data, which is the nth root of the product of n values. The geometric mean is useful for data that are products or rates, such as growth rates, concentrations, or normalized values. It is always less than or equal to the arithmetic mean and is only defined for positive numbers. This function flattens the input data, ignores non-numeric values, and operates on the entire dataset. For more details, see the scipy.stats.gmean documentation .
This example function is provided as-is without any representation of accuracy.
Usage
To use the function in Excel:
=GMEAN(data)data(2D list, required): Table of values (must be positive numbers). All values are flattened and non-numeric values are ignored.
The function returns a single value (float): the geometric mean of the input data, or an error message (string) if the input is invalid.
Examples
Example 1: Simple Geometric Mean
Inputs:
| data | |
|---|---|
| 1 | 2 |
| 3 | 4 |
Excel formula:
=GMEAN({1,2;3,4})Expected output:
| Result |
|---|
| 2.2134 |
Example 2: Including Non-Numeric Values
Inputs:
| data | |
|---|---|
| 2 | ”a” |
| 8 | 4 |
Excel formula:
=GMEAN({2,"a";8,4})Expected output:
| Result |
|---|
| 4.0 |
Example 3: Single Row
Inputs:
| data | |
|---|---|
| 5 | 10 |
Excel formula:
=GMEAN({5,10})Expected output:
| Result |
|---|
| 7.0711 |
Example 4: Single Column
Inputs:
| data |
|---|
| 2 |
| 8 |
| 32 |
Excel formula:
=GMEAN({2;8;32})Expected output:
| Result |
|---|
| 8.0 |
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.
Args:
data: 2D list of values (must be positive numbers).
Returns:
float: Geometric mean of the input data, or error message (str) if input is invalid.
This example function is provided as-is without any representation of accuracy.
"""
# Validate input type
if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
return "Invalid input: data must be a 2D list."
# Flatten the 2D list and filter for positive numeric values
try:
flat = []
for row in data:
for x in row:
try:
val = float(x)
if val > 0:
flat.append(val)
except Exception:
continue
if not flat:
return "Input must contain at least one positive number."
result = scipy_gmean(flat)
return round(float(result), 4)
except Exception as e:
return f"An error occurred during calculation: {e}"Live Notebook
Edit this function in a live notebook .