The HARMEAN function in Google Sheets, which is categorized under the Statistical functions, returns the harmonic mean of a dataset.

The harmonic mean is one of several types of numerical average and useful for calculating the average of rates.

To understand the Google Sheets HARMEAN function, you must first understand ‘reciprocals’. Why?

This is because the HARMEAN (harmonic mean) of a dataset is the reciprocal of the arithmetic mean of the reciprocals.

Better understand it form the following generic Formula.

Harmonic Mean = Size of population / Sum of reciprocals of each of the data points in the population

In this general formula, the term ‘reciprocal’ simply means 1/data point.

The arithmetic mean is sum/count whereas the reciprocal of the arithmetic mean is count/sum. The harmonic mean (reciprocal of the arithmetic mean of the reciprocals) is count/sum of reciprocals.

For example, the harmonic mean of the data points (values) 1, 3, 5 are as follows.

=3/(1/1+1/3+1/5)

This formula would return 1.957.

Explanation:

3 is the data point size which is the count of data points.

1/1+1/3+1/5 – this will return the sum of reciprocals of each of the data points in the population which would be equal to 1.533333333.

The above calculation is simple to perform with the help of the HARMEAN function in Google Sheets.

HARMEAN Function Syntax and Arguments in Google Sheets

Syntax:

HARMEAN(value1, [value2, ...])

Arguments:

value1 – (required) the first value/range of the population.
value2 – (optional) additional values/ranges to include in the population.

Example

Example 1: To understand the first argument which is ‘value1’ (range).

Harmean of the values 1, 3, and 5 from the range B2:B4.

Example 2: To understand the first and second arguments which are ‘value1’ and ‘value2’ (range 1 & range 2).

Harmean of the values 1, 3, and 5 from the range B2:B4 and values 2, 4, 8, and 9 from the range C2:C5.

Since the data points (values) are from the adjoining columns, we can use the above range in the Harmean function as below.

=HARMEAN(B2:C5)

Common Errors

#NUM! – The HARMEAN function in Google Sheets will return this error if the values are formatted to text.

Also if any of the values (data points) in the ‘range’ is any type of error (as part of another formula output), the same error will be returned in the harmonic mean calculation.

So use IFERROR as below with Google Sheets HARMEAN formula to avoid such errors from happening.

=HARMEAN(iferror(C2:C5))

Similar Articles

1. How to Use the TRIMMEAN Function in Google Sheets.
2. Google Sheets Average Function [Advanced Tips and Tricks].
3. AVERAGE.WEIGHTED: Calculate the Weighted Average in Google Sheets.
4. DAVERAGE Function in Google Sheets – Formula Examples.
5. Average Array Formula Across Rows in Google Sheets.