CODEWITHSUNDEEP
pythonexcelweighted-averagegeometric-meanlarge-data
user1595970
user1595970

Reputation: 11

calculating weighted geometric mean of a large list of numbers

I am trying to calculate a weighted geometric mean of a large list of number (about 115k numbers).

Each number has a weight value of either 1, 2, or 3 assigned.

There seem to be a ton of ways to calculate geometric mean ( non-weighted ), and one thing I've ran into before is that the method:

  1. EXP ( AVERAGE ( LN ( < list of values > ) ) )

is a much better method for large lists than either of these:

  1. PRODUCT ( nTH_ROOT ( ) ), where n is the number of values in the list

  2. nTH_ROOT ( PRODUCT ( ) ), where n is the number of values in the list

The references I could find for calculating weighted geometric mean all used the last two methods. In my limited experience, those methods almost always result in an error when trying to run them over a very long list. For a list of 115k, method number 2 calculates an intermediate number that is ungodly large. Excel is not capable of taking the 115000th root of a number, I am not sure if something like python or R could do it.

Anyway, my goal is to come up with a weighted geometric mean formula based on method 1 above.

My proposed solution is below. One note: I'm doing this in excel mostly, although I calculated the log of each value in the list in python first. My question isn't about programming in excel, its more about the math behind weighted geometric mean. I bring up excel because my proposed solution below is based on using excel pivot table output to group the list values based on the weight. My eventual output needs to be in excel, so excel-friendly solutions are preferred. Its testing out well in excel, but I wanted to ask for confirmation.

weighted geometric mean = 

EXP(
 AVERAGE ( LN ( <list values where weight is 1> ) )  *  ( 1 / 6  ) 
 + AVERAGE ( LN ( <list values where weight is 2> ) )  *  ( 2 / 6 )
 + AVERAGE ( LN ( <list values where weight is 3> ) )  *  ( 3 / 6 ) 
)

Thanks a bunch!

Paul

Some links that were almost helpful:

https://www.quora.com/How-do-I-calculate-a-weighted-geometric-mean

http://www.dummies.com/education/math/business-statistics/how-to-find-the-weighted-geometric-mean-of-a-data-set/

Upvotes: 1

Views: 2679

Answers (1)

msitt
msitt

Reputation: 1237

The weighted average geometric mean can be calculated using this formula:

enter image description here

So in Excel, it would be

=EXP(SUMPRODUCT(weights, LN(x)) / SUM(weights))

Upvotes: 1

Related Questions