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

This article was published on February 6, 2010 by Al Gulland.

2 comments on “Calculating Geometric Mean in Web Intelligence

  1. Pingback: When analysis is better using Web Intelligence than Excel | Business Intelligence Articles from www.gulland.com

Leave a Reply