Found this old thread & am hoping you can help.
For the future, it is not a good idea to post a new question by "responding"
in an old thread, especially a 3-year thread. People use many difference
interfaces for looking at these newsgroups, and sometimes it is difficult or
impossible to see the context of the new "response".
It is better to copy the relevant text any previous message, and paste it
into a new thread. Or simply rephrase your question completely without
referring to any old discussions.
I assume your formulas are for log(10).
Any changes for log(2)?
I will answer that below.
But before I do, I must ask: why are you using log-base-2 in the first
place?
That is part curiosity -- what application/purpose uses log-base-2? -- and
part rhetorical.
The geometric mean and std dev should be about the same regardless of the
base.
Financial calculations often use the natural log. I wonder if that is what
you mean by "log(2)" [sic]. That requires a different set of functions.
For any integer base "b", the array-entered formulas are (press
ctrl+shift+Enter instead of just Enter):
geometric mean:
=b^AVERAGE(LOG(1+A1:A100,b)) - 1
geometric std dev (of population):
=b^STDEVP(LOG(1+A1:A100,b)) - 1
geometric sample std dev:
=b^STDEV(LOG(1+A1:A100,b)) - 1
std err of the geometric mean (not errata):
=b^(STDEVP(LOG(1+A1:A100,b))/SQRT(COUNT(A1:A100)-1))) - 1
or
=b^(STDEV(LOG(1+A1:A100,b))/SQRT(COUNT(A1:A100))) - 1
depending on which std dev (population or sample) applies to your
application.
However, for the natural log, the array-entered formulas are (press
ctrl+shift+Enter instead of just Enter):
geometric mean:
=EXP(AVERAGE(LN(1+A1:A100,b))) - 1
geometric std dev (of population):
=EXP(STDEVP(LN(1+A1:A100,b))) - 1
geometric sample std dev:
=EXP(STDEV(LN(1+A1:A100,b))) - 1
std err of the geometric mean (not errata):
=EXP(STDEVP(LN(1+A1:A100,b))/SQRT(COUNT(A1:A100)-1))) - 1
or
=EXP(STDEV(LN(1+A1:A100,b))/SQRT(COUNT(A1:A100))) - 1
As I noted previously, depending on your application/purpose, it is probably
better to use the average and std dev of the log values for any calculations
based on the geometric statistics (e.g. Monte Carlo simulation), then take
the antilog of the result.
If that is unclear, post an example of the calculation that uses geometric
statistics, and we might be able to demonstrate the correct usage.