Calculating Geometric Mean in Web Intelligence

A geometric mean is an average that is useful for sets of numbers that are interpreted according to their product and not their sum as is the case with the more commonly known arithmetic mean. An example of where you would use a geometric mean is in financial reporting when you want to calculate the compound annual growth rate.

Compound annual growth rate is an average growth rate over a period of several years. For example if you had growth rates of 4%, 6%, 5%, 8% each year what was the average rate over the four year period? You can then use this average to forecast growth for the next few years.

Web Intelligence doesn’t have a built in geometric mean formula however we can calculate it using logarithms. This article then looks at how to calculate a geometric mean in Web Intelligence.

Usage

Compound annual growth rate mentioned above is an example of where you need to use a geometric mean and a good explanation of why using a normal arithmetic average wont work is given by monyterms website:

If a company’s sales rose from £10m in year one to £15m in year two and then fell back to £10m in year three, then there has been a 50% increase (year-on-year) followed by a 33% decrease (year-on-year). Adding these up would give 17% and therefore an arithmetic mean of 8.5%, whereas it is obvious that the average growth has been 0%. A geometric average gives the correct answer.

Further examples of when to use a geometric mean can be found at the University of Toronto’s Maths Network.

Calculating in Web Intelligence

There isn’t a geometric mean formula available in Web Intelligence and so we must calculate this using other formula.

To determine the geometric mean you can first calculate the arithmetic mean of the logarithms of each value and then using exponentiation to calculate the geometric mean. It is beyond this article to explain why this is the case but it does give the same result.

Let us look at a worked example. We begin with the following table and we want to calculate the average percentage change in stock value over the 4 year period.

Year	Stock Value	Percentage Difference
2000	123	100.00%
2001	134	108.94%
2002	139	103.73%
2003	148	106.47%

First let us add a new column and in this column calculate the natural log of the percentage change using the ln() formula.

Year	Stock Value	Percentage Difference	Ln
2000	123	100.00%	0
2001	134	108.94%	0.08566
2002	139	103.73%	0.03663
2003	148	106.47%	0.06274

Then calculate the average of these logs by inserting the average formula in the table footer. If you switch to View Structure your table should look like,

Year	Stock Value	Percentage Difference	Ln
[year]	[stock value]	[percentage difference]	=ln([percentage difference])
			=Average([ln[percentage difference]])

Finally we take the exponent of the average,

Year	Stock Value	Percentage Difference	Ln
[year]	[stock value]	[percentage difference]	=ln([percentage difference])
			=Exp(Average([ln[percentage difference]]))

Switching back to View Results displays,

Year	Stock Value	Percentage Difference	Ln
2000	123	100.00%	0
2001	134	108.94%	0.08566
2002	139	103.73%	0.03663
2003	148	106.47%	0.06274
			1.06361

So on average the stock value rose by 6.36% each year.

Putting it all together

We don’t have to add this extra column that calculates the logs, we can just and the final formula we have in the footer directly below the percentage difference column. We can also convert this to a percentage by subtracting 1 and then formatting the cell as a percentage.The formula to use would be,

=Exp(Average([ln[percentage difference]]))-1

Lastly we want to display the percentage difference column as percentage growth

Year	Stock Value	Growth
2000	123	0.00%
2001	134	8.94%
2002	139	3.73%
2003	148	6.47%
	Average:	6.36 %

 

Further Reading and References

• Applications of the Geometric Mean
• The Average Guy’s Guide to EPS Growth Rates
• Calculating Geometric Means
• CAGR (compound annual growth rate)