Geometric Standard Deviation
Sandman
I found the function for the geometric mean but have not been able to figure
out how to calculate the GSD.
--
Sandman
JoeU2004
The population and sample standard errors can be computed with the
appropriate following array formula (commit with ctrl+shift+Enter instead of
Enter):
=10^STDEVP(LOG(1+G2:G100)) - 1
=10^STDEV(LOG(1+G2:G100)) - 1
where G2:G100 are the growth rates Y1/Y2 - 1, Y2/Y3 -1, etc to Y99/Y100 - 1,
where Yn is the data for time "n".
By the way, the geometric mean can be computed with the following array
formula:
=10^AVERAGE(LOG(1+G2:G100)) - 1
That will work in situations where GEOMEAN does not.
Do you need to know how to compute the geometric standard error of the mean?
Sandman
my data.
--
Sandman
JoeU2004
I wrote:
> where G2:G100 are the growth rates Y1/Y2 - 1, Y2/Y3 -1, etc
> to Y99/Y100 - 1, where Yn is the data for time "n".
That's okay if the data is sorted is descending time order. But I wrote "Yn
... for time n", meaning time 1, 2, etc. Normally that implies ascending
time order. In that case, the growth rates are typically Y2/Y1 - 1,
Y3/Y2 -1, etc to Y100/Y99 -1.
----- original message -----
"JoeU2004" <joeu...@hotmail.com> wrote in message
news:%23CyWL3n...@TK2MSFTNGP05.phx.gbl...
JoeU2004
> Thanks Joe. I could not find calculations steps anywhere.
> Now I can analyze my data.
You're welcome. But be careful with how you use these statistics to analyze
data. In short, I would suggest that you do your statistical analysis on
the log values, converting to the antilog only at the last step, if at all.
For example, if the geometric mean and std dev are u and s, you cannot say
that some data point Gn is z = (Gn - u)/s std devs from the mean.
Similarly, the standard error of the mean is not SE = s/SQRT(n). [1]
Moreover, if you are analyzing periodic changes of stock prices, note that
it is the logs of historical return rates that people say are normally
distributed, not the return rates themselves.
Endnotes:
[1] FYI, the population and sample geometric standard errors of the mean can
be computed with the appropriate following array formula (commit with
ctrl+shift+Enter instead of Enter):
=10^(STDEVP(LOG(1+G2:G100)) / SQRT(COUNT(G2:G100))) - 1
=10^(STDEV(LOG(1+G2:G100)) / SQRT(COUNT(G2:G100))) - 1
----- my previous posting (corrected) -----
"JoeU2004" <joeu...@hotmail.com> wrote in message
news:%23CyWL3n...@TK2MSFTNGP05.phx.gbl...
> "Sandman" <San...@discussions.microsoft.com> wrote:
>> Can the geometric standard deviation of a dataset be calculated using
>> Excel?
>> I found the function for the geometric mean but have not been able to
>> figure
>> out how to calculate the GSD.
>
> The population and sample standard errors can be computed with the
> appropriate following array formula (commit with ctrl+shift+Enter instead
> of
> Enter):
>
> =10^STDEVP(LOG(1+G2:G100)) - 1
>
> =10^STDEV(LOG(1+G2:G100)) - 1
>
> where G2:G100 are the growth rates Y2/Y1 - 1, Y3/Y2 -1, etc to Y100/Y99 -
NoKiE
NoKiE
So how to calculate standard deviation of geometric mean?
JoeU2004
> So how to calculate standard deviation of geometric mean?
I answered that question in my first response in the thread to which you
posted this response. How could you miss it? You are using the MS
Discussion Groups web interface. Using that interface, I can see the entire
thread, started on July 21.
Anyway....
----- my previous response -----
"JoeU2004" <joeu...@hotmail.com> wrote in message
news:%23CyWL3n...@TK2MSFTNGP05.phx.gbl...
> "Sandman" <San...@discussions.microsoft.com> wrote:
>> Can the geometric standard deviation of a dataset be calculated using
>> Excel?
>> I found the function for the geometric mean but have not been able to
>> figure out how to calculate the GSD.
>
> The population and sample standard errors can be computed with the
> appropriate following array formula (commit with ctrl+shift+Enter instead
> of
> Enter):
>
> =10^STDEVP(LOG(1+G2:G100)) - 1
>
> =10^STDEV(LOG(1+G2:G100)) - 1
>
> where G2:G100 are the growth rates Y1/Y2 - 1, Y2/Y3 -1, etc to Y99/Y100 -
> 1,
> where Yn is the data for time "n".
>
> By the way, the geometric mean can be computed with the following array
> formula:
>
> =10^AVERAGE(LOG(1+G2:G100)) - 1
>
> That will work in situations where GEOMEAN does not.
>
> Do you need to know how to compute the geometric standard error of the
> mean?
----- original message -----
"NoKiE" <No...@discussions.microsoft.com> wrote in message
news:E7C5A31B-E69D-4582...@microsoft.com...
brant....@gmail.com
Found this old thread & am hoping you can help. I assume your formulas are for log(10). Any changes for log(2)?
joeu2004
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 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.
joeu2004
> 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
non-zero; for example, a percentage change y/x - 1.
If A1:A100 contains just an always-positive (non-zero) ratio y/x, replace
1+A1:A100 with simply A1:A100 in all of the formulas posted previously.
Michael Marshall
=njsGEOSTDEV( { 2.25, 1.75, 3.25, 1.45, 2.35, 1.5, 1.65, 2.25, 1.35 } )
One may also enter the data set as a reference to a cell range containing the values
=njsGEOSTDEV( A1: I1 )
njsGEOSTDEV is a 3rd party Excel geometric standard deviation function http://njsxl.njinstruments.com/excel-geometric-standard-deviation-function.html that is part of njsXL add-in http://njsxl.njinstruments.com/