Web Tortoise


Geometric Mean in Excel

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


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


Christmas in Excel


Hello! This was written 2012-MAR-07 at 01:42 PM ET.

Use color to add value to your Performance charts. Red and green columns overlay your line chart where red equals an “increase in Response time from previous interval” and green equals “decrease in Response time from previous interval”.

The following excel waterfall chart has the aforementioned conditions applied (red equals an “increase in Response time from previous interval” and green equals “decrease in Response time from previous interval”). In this case, the waterfall chart is showing week-over-week Response time for a landing page. Credit to peltiertech.com for the waterfall template; my additional formatting has been applied.

Excel Waterfall

Document Complete / OnLoad:

_The following is optional reading material._

Suppose you’re an academic site, perhaps a Search Engine or a Reference site. You might theorize the September change in Response time is impact from everyone going ‘back to school’. To further your theory, notice the December change, when everyone is ‘home for the holidays’. What else might you look for? Maybe three ‘reds in a row’, to spot a trend?

Download excel file: https://docs.google.com/open?id=0B9n5Sarv4oonSEhDZklWb3RUZWl5LTVlcE1sbUpTdw

Search for the PeltierTech article:  http://www.ask.com/web?q=excel+waterfall+chart+peltiertech

# Excel conditional formatting ; Excel conditional charting ; Web Performance Optimization ; Catchpoint ; Keynote ; Gomez ; Excel ; Statistics

Blog at WordPress.com.