They don't teach these mean Excel functions in school but they're super handy

The simplest way to find the average (mean) of a set of numbers is to use the AVERAGE function. This calculation gives you the arithmetic mean, which sums up all the numbers and divides the result by the count of values in the dataset. However, this might not always give you an accurate average, which is where functions like GEOMEAN and HARMEAN come in.

The GEOMEAN and HARMEAN are advanced statistical functions that typically require a deeper understanding of mathematics. This is probably why they aren't taught as often as the AVERAGE function. However, in practical scenarios like Excel, you simply need to understand what they do, why they're useful, and when to use them.

Exploring the GEOMEAN function

The mean for multiplicative data

To understand GEOMEAN, you need to know what the geometric mean is. It's what you get when you take the product of numbers and raise it to the power of 1 divided by the number of values (1/n). Although this may sound complicated, it is actually easy to understand. Let's break it down with an example.

To get the product of a set of numbers, you just multiply them together. Here's an example formula:

        =5*10*20

    

This will give you 1,000. Now, since there are three values, we will raise it to the power of 1/3. Essentially, we are taking the cube root of the product to get the geometric mean.

        =1000^(1/3)

    

You can put all of this in one formula if you want to.

        =(5*10*20)^(1/3)

    

This will give you 10 as the geometric mean. As you can see, these steps give you greater control of the formula. You can even make it clearer by using the LET function.

Alternatively, you can use the GEOMEAN function and get the same result. The syntax is similar to the AVERAGE function.

        =GEOMEAN(5, 10, 20)

    

When to use GEOMEAN

The beauty of GEOMEAN is that it's less affected by extreme numbers, whether they're small or large. It provides a balanced average that is not skewed by extreme cases. This is particularly important in financial analysis and scientific research, where outliers can significantly distort the arithmetic mean, leading to incorrect insights.

But most importantly, GEOMEAN is used when you need to account for compounding. This is common when calculating something like the growth rate of an investment.

Suppose you have a $1,000 investment and these are the returns in percentages over five years (based on the original amount): 110%, 77%, 98%, 120%, and 125%. 110%. This means the investment grew by 10% of its original value in the first year, it dropped by 23% of its original value in the second year, and so on. So, the compound returns over that same period would be $1,100, $847, $830.06, $996.07, and $1245.09.

If you want to find the average rate of growth for this investment using the arithmetic mean, you would do the following:

        =AVERAGE(110, 77, 98, 120, 125)

    

This will give you 106%, meaning the annual compound growth of that investment is 6%. This is not accurate because if you took it and multiplied it by $1,000 over a period of five years, the compound returns would be $1060, $1123.60, $1191.02, $1262.48, and $1338.23. Furthermore, if we have larger or smaller percentages, these extreme numbers would have heavily skewed the result further.

Now let's do the GEOMEAN instead.

        =GEOMEAN(110, 77, 98, 120, 125)

    

This will give you 104.48%, meaning the accurate annual compound growth of that investment is 4.48%. If you multiply it by $1,000 over five years, you will get $1,044.82, $1,091.64, $1,140.57, $1,191.68, and $1,245.09. The most important number here is the last one ($1,245.09), which is the same as the one we got when we calculated the compound returns.

Exploring the HARMEAN function

Give more weight to smaller numbers

High values can sometimes give you an inaccurate average, and the harmonic mean gives them less weight, so the lower values can shine. It does this by averaging reciprocals—flips the numbers to get the reciprocals (e.g., 4 becomes 1/4), averages them, and then flips them back.

If you were to do the harmonic mean manually, you first count the values you’re averaging. Assuming the values are in A1:A5, you would enter the following formula in C1.

        =COUNT(A1:A5)

    

In C2, you would sum the reciprocals using the SUMPRODUCT function.

        =SUMPRODUCT(1/A1:A5)

    

In C3, you would flip the values back to get the harmonic mean.

        =C1/C2

    

This is what it would look like as a single formula.

        =COUNT(A1:A5)/SUMPRODUCT(1/A1:A5)
    

Alternatively, you can just use the HARMEAN function, and the result will be the same.

        =HARMEAN(A1:A5)

    

When to use HARMEAN

The HARMEAN function is best used for averaging rates and ratios. That's because it prevents larger values from dominating the result and gives each value the appropriate weight.

Let's say you're trying to measure the average speed cars travel on a particular road. Here is what using the AVERAGE function would look like.

        =AVERAGE(20, 45, 100, 60, 80, 180)

    

This will give you 80.83333333 as the average, which is high because of the outlier in the data (180).

Using the harmonic mean reveals a much smaller average of 51.3064133, as there's less emphasis on the outlier.

        =HARMEAN(20, 45, 100, 60, 80, 180)

    

They should really teach GEOMEAN and HARMEAN in school

GEOMEAN and HARMEAN are powerful tools that need to be taught after users are familiar with the traditional AVERAGE function. They are valuable alternatives that can be more accurate in certain scenarios. The good thing is that you can still use them effectively by understanding that GEOMEAN works well for compounding data and HARMEAN for rates and ratios.