Calculating Geometric Standard Deviation Using Microsoft Excel

Statistics can be quite a cool thing to learn. The great skill comes handy in plenty of fields, including science, finance, and even sociology. However, some of its terminologies are straight up mind-boggling. But fear no more, though. Because today we will demystify one often confused concept called Geometric Standard Deviation (GSD).

Now, before you freak out at how complex the word is, let’s define it in the simplest way. This big term simply describes how much spread there is in a set of numbers from the geometric mean for a certain variable.

To be able to calculate this, you first need to calculate the geometric mean. Now, mean is basically a central tendency of a set of values. The geometric version is different from the arithmetic version, though. It uses the product of the dataset instead of their summation.
The geometric mean (and, thus, the geometric standard deviation) is commonly used by accountants and business professionals in the calculation of investment returns. This is especially true when compound interest is involved.

So, now that we know what we’re talking about, let’s look into how to calculate it. Of course, you can easily get the formula online and do it with your calculator. But if you are analyzing a lot of data, you may not have the time to do all this manually.
So let’s talk about the easiest way to do it using the most common data analysis software available: Microsoft Excel.

As mentioned earlier, you must first calculate your geometric mean. You can easily do this with the preinstalled GEOMEAN function. Just arrange your set of values in a column and apply the function at the bottom.

However, you can also use a different kind of formula according to your reference. In case you are using the Wikipedia version, arrange the values in a row as described previously. For a data set between cells B1 to B102, use:
=EXP(STDEV(LN(B1:B102)))
This will give you the standard deviation of the log values you had between cells B1 to B102. But hold on, we’re not done yet. You now have to add an average of the log values to the arithmetic standard deviation followed by the EXP function. Simply arrange the function as follows:
=EXP(STDEV(LN(B1:B102))+AVERAGE(LN(B1;B102)))

You should note, however, that you must enter this formula as an array. Also, this approaches will give you a significantly different result from the one you would expect to get from the simpler Wikipedia formula version. So, which one will give you the most accurate answer? That is a matter of debate by the experts depending on the definition and derivation of the formula.

Leave a Reply

Your email address will not be published. Required fields are marked *