How to calculate weighted geometric mean in excel?

**_hs_** · 04-14-2013, 03:33 PM

Dear users,

Does anyone know whether it is possible to calculate a weighted geometric mean in excel?

Any help with this would be much appreciated!

/hs

**loginjmor** · 04-14-2013, 04:40 PM

Hi -

Shouldn't be too difficult. You would need a column for your observations (say column X), a column for your weights (say column W), and a column to multiply W*ln(X) (Let's call this column Y).

Basically, the formula would be:

=exp((sum(Y1:Yn)/sum(W1:Wn)) where n is the number of observations. For example if you had 5 observations and 5 weights the formula would be =exp((sum(Y1:Y5)/sum(W1:W5))

Hope this helps.

**_hs_** · 04-15-2013, 03:32 AM

Hi there, thanks a lot for this reply, that is very kind of you!
What I was thinking is that there might be a way to skip the middle step calculating column Y? Kind of along the same line as the Sumproduct-funtion. I have approx. 40 40x40 matrices for which I have to do it, so any reduction of middle steps would save me a lot of time:-)
In any event thanks a lot for your help!
/hs

**daddylonglegs** · 04-15-2013, 05:01 AM

using loginjmor's example you could use this formula which follows his method but doesn't need a helper column

=EXP(SUMPRODUCT(W1:W5,LN(X1:X5))/SUM(W1:W5))

or use this "array formula"

=PRODUCT(X1:X5^(W1:W5/SUM(W1:W5)))

confirmed with CTRL+SHIFT+ENTER

in both cases the ranges need to be fully populated (no blanks) - they should give the same results

**_hs_** · 04-15-2013, 10:56 AM

Thanks a lot to both of you that is perfect!

All the best

**rosalieghislain** · 11-08-2020, 09:19 AM

Hi,

What do I have to put in the "weight" column? Numbers from 1 (most important) to 5 (less important) or percentages?

**AliGW** · 11-08-2020, 09:42 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread