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.

Contents

#### 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)

Great Post!

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