HARMEAN Function in Excel – Get Harmonic Mean

In one of the previous blogs, we did the usage of the AVERAGE Function to calculate the arithmetic mean of data. In this blog, we are going to learn another important Statistical Function, known as HARMEAN Function in Excel.

So let’s move ahead.

When to Use HARMEAN Function in Excel?

As the function name says, the HARMEAN Function calculates the Harmonic Mean for a set of data containing numerical values. The harmonic mean is different from the arithmetic mean which is why the HARMEAN Function is used in special cases.

The HARMEAN Function came into existence in Excel 2003 but its functionality was extended in terms of the number of arguments in Excel 2007.

Syntax and Arguments

=HARMEAN(numbre1,number2……)

The following points define the inputs required by the HARMEAN function in Excel.

• number1 – This inputs the numerical data set or value for which we want to get the Harmonic mean. It can be a reference or direct numerical value.
• [number2] – This argument is not mandatory to input, but works the same as the number1 argument.

If you have Excel 2007 or later versions, the HARMEAN Function will have a maximum of 255 such number inputs. But for Excel 2003, there would be 30 maximum inputs. Each number argument can be a cell reference, a range of cells, a named range, a hardcoded number or an array of values.

What is Harmonic Mean?

The Harmonic mean refers to the reciprocal of the arithmetic mean of numerical reciprocals. Now, this might seem intimidating at first, so we understand with a formula.

Let us suppose we have a certain set of data containing n numerical values.

The values go from a,b,c,d…… up to n values.

The arithmetic mean of the reciprocals of these numerical values would be:

AM= (1/a + 1/b + 1/c + …. up to n terms ) / n

where n is the total number of numerical values.

The reciprocal of this Arithmetic mean (AM) is known as Harmonic Mean.

HM= n / ( 1/a + 1/b + 1/c +…. up to n terms)

Why We Use Harmonic Mean?

The Harmonic mean is particularly used for ratios and rates.

The Arithmetic mean gives higher weight to larger values while the Geometric mean assigns a higher weight to lower values. Only the Harmonic mean assigns equal weight to every numerical value in the data set.

The value of the Harmonic mean is always less than the Arithmetic and Geometric mean for a given set of values.

Examples to Calculate Harmonic Mean in Excel

In his section, we would implement the HARMEAN Formula.

Example 1 – Direct Input to HARMEAN Formula

This is going to be a basic example. Let us suppose we have the following data set.

{21,24,45,34,23,53,67,23,12}

We want to find the Harmonic Mean for this data. Use any of the following HARMEAN Formula as both formulas would return the same result.

=HARMEAN(21,24,45,34,23,53,67,23,12)

or

=HARMEAN({21,24,45,34,23,53,67,23,12})

As a result, the formula gives the Harmonic mean as 26.09

Explanation – We have supplied the number arguments directly with the numerical values 21,24,45,34,23,53,67,23, and 12. The function evaluates the harmonic mean.

In the alternate formula, we have supplied the data in the form of an array of values through the use of any first number1 argument. We had enclosed the values in brackets {21,24,45,34,23,53,67,23,12} so it becomes a single array which could be supplied as single argument.

Consequently, the function evaluates the harmonic mean.

Example 2 – Using References for HARMEAN Formula

In this example, we are going to take the data in Excel range of cells as follows.

As you can see, we got 12 numerical values divided into two ranges A2:A7 and C2:C7.

Use the following HARMEAN Formula to get the harmonic mean for these 12 numerical values.

=HARMEAN(A2:A7,C2:C7)

As a result, the formula returns the value of the harmonic mean as 18.86

Explanation – We have supplied two ranges A2:A7 and C2:C7 as the number1 and number2 inputs of HARMEAN formula. The function evaluates the harmonic mean of numbers contained in these two ranges.

Important Points about HARMEAN Formula

There are some important notes about the HARMEAN Function of Excel.

• You cannot supply negative values as formula input or you would get a #NUM! error.
• If the input cell reference contains text values, logical values or empty cells, the HARMEAN Function ignores them while this does not apply on zero.
• If we directly supply any logical value ( TRUE or FALSE ) then it would be interpreted as 1 or 0 respectively.
• A directly supplied text string would be considered as 0.

This brings us to the end of the blog.

Thank you for reading.