HMEAN
Overview
The HMEAN function calculates the harmonic mean of a dataset. The harmonic mean is the reciprocal of the arithmetic mean of the reciprocals of the observations. It is most appropriate for situations when the average of rates is desired. For example, the harmonic mean is the correct mean to use for averaging speeds.
The formula for the harmonic mean is:
where x_i are the individual data points and n is the number of data points.
For more details, see the scipy.stats.hmean documentation .
This example function is provided as-is without any representation of accuracy.
Usage
To use the function in Excel:
=HMEAN(data)data(2D list, required): A 2D array of numbers. Non-numeric and non-positive values are ignored.
The function returns a single value (float): the harmonic mean of the data, or an error message (string) if the input is invalid or contains no positive numeric values.
Examples
Example 1: Simple Harmonic Mean
This example calculates the harmonic mean for a small 2x2 matrix.
Inputs:
| data | |
|---|---|
| 1 | 2 |
| 3 | 4 |
Excel formula:
=HMEAN({1,2;3,4})Expected output:
| Result |
|---|
| 1.92 |
Example 2: Column Vector
This example calculates the harmonic mean for a column of data.
Inputs:
| data |
|---|
| 2 |
| 4 |
| 8 |
| 16 |
Excel formula:
=HMEAN({2;4;8;16})Expected output:
| Result |
|---|
| 4.2667 |
Example 3: Ignoring Non-Numeric Values
This example shows how the function ignores text values within the data range.
Inputs:
| data | |
|---|---|
| 5 | 6 |
| ”text” | 7 |
Excel formula:
=HMEAN({5,6;"text",7})Expected output:
| Result |
|---|
| 5.8879 |
Example 4: Larger Dataset
This example calculates the harmonic mean for a larger set of numbers.
Inputs:
| data | ||
|---|---|---|
| 10 | 20 | 30 |
| 40 | 50 | 60 |
Excel formula:
=HMEAN({10,20,30;40,50,60})Expected output:
| Result |
|---|
| 24.4898 |
Python Code
import micropip
await micropip.install('scipy')
from scipy.stats import hmean as scipy_hmean
def hmean(data):
"""
Calculates the harmonic mean of the input data.
Args:
data (list[list]): A 2D list of numbers. Non-numeric and non-positive values are ignored.
Returns:
float: The harmonic mean of the data.
str: An error message if the input is invalid or contains no positive numbers.
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
flat_data = []
for row in data:
for item in row:
try:
val = float(item)
if val > 0:
flat_data.append(val)
except (ValueError, TypeError):
continue # Ignore non-numeric values
# Check if there is any valid data
if not flat_data:
return "Invalid input: data must contain at least one positive number."
# Calculate harmonic mean using scipy's hmean
try:
result = scipy_hmean(flat_data)
except Exception as e:
return f"An error occurred during calculation: {e}"
return round(result, 4)Live Notebook
Edit this function in a live notebook .