Response:
Hello! This was written 2012-MAR-16 at 11:38 AM ET.
Question: How do you calculate the geometric mean in excel? Unfortunately, as of Excel version 14.0 and earlier, the built-in GEOMEAN function is partially broken and does not do what’s needed when working with larger data sets.
Answer: Download the below excel file to see actual data and actual calculations. Otherwise, here are the basic steps to calculate the Geometric Mean in Excel:
Step 01. Have some data. For example, consider data 102, 1024 and 956.
Step 02. Take the logarithm (LOG) of this data. The base 10 LOG of 102 is 2.008600172, the LOG of 1024 is 3.010299957 and the LOG of 956 is 2.980457892.
Step 03. Take the arithmetic mean average of all the logs. The arithmetic mean average of the three logs is 2.666452674 ((2.008600172 + 3.010299957 + 2.980457892) divided by 3) = 2.666452674.
Step 04. The Geometric Mean of the data 102, 1024 and 956 is 463.9302313. To get this, take the anteLOG of Step .03’s result. The anteLOG of 2.666452674 is 463.9302313 (10 ^ 2.666452674 = 463.9302313.
Note any base will work for calculating the LOG and subsequent anteLOG. The default is base 10.
Note the data should all be greater than zero 0. In our world of measuring Performance and Response Time, this should always be the case.
Credit to http://www.thinkingapplied.com/means_folder/deceptive_means.htm .
Document Complete / OnLoad:
_The following is optional reading material._
Important: You can take the Arithmetic Mean of other Arithmetic Means because the functions are additive. You MUST NOT take the Geometric Mean of other Geometric Means because their functions are not additive.
Download excel file: https://docs.google.com/open?id=0B9n5Sarv4oonN2xPVXg0U2hRSG1kYWc1VkU0ZkI3Zw
# Catchpoint ; Keynote ; Gomez ; Excel ; Statistics ; Excel Statistics
Web Tortoise 