Web Tortoise

2012-Mar-16

Geometric Mean in Excel

Filed under: Performance — leovasiliou @ 11:57 AM EDT

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

Download PDF of the deceptive means article: https://docs.google.com/open?id=0B9n5Sarv4oonWkh1QVhzQm9RcFcwbzR5ZFljYmp6dw

# Catchpoint ; Keynote ; Gomez ; Excel ; Statistics ; Excel Statistics

2 Comments »

  1. […] #- How do I calculate the geometric mean in Excel? […]

    Pingback by WebTortoise Year in Review 2012 « Web Tortoise — 2012-Dec-20 @ 03:37 PM EDT

  2. […] #- See, “How do I calculate the Geometric Mean in Excel”? […]

    Pingback by Arithmetic Mean Versus Geometric Mean Versus Median « Web Tortoise — 2013-Jan-31 @ 09:22 PM EDT


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: