Geometric Standard Deviation

  • Thread starter Thread starter Sandman
  • Start date Start date
S

Sandman

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.
 
Sandman said:
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.
Click to expand...

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?
 
Errata....
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".
Click to expand...

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 -----
 
Sandman said:
Thanks Joe. I could not find calculations steps anywhere.
Now I can analyze my data.
Click to expand...

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 said:
Sandman said:
Thanks Joe. I could not find calculations steps anywhere.
Now I can analyze my data.
Click to expand...

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 said:
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 -
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?
Click to expand...
Click to expand...
 
NoKiE said:
So how to calculate standard deviation of geometric mean?
Click to expand...

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 said:
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?
Click to expand...


----- original message -----
 
@ JoeU2004

Found this old thread & am hoping you can help. I assume your formulas are for log(10). Any changes for log(2)?
 
Found this old thread & am hoping you can help.
Click to expand...

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)?
Click to expand...

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.
 
Clarification.... I wrote:
joeu2004 said:
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
Click to expand...

Those formulas assume that A1:A100 contains a ratio minus 1, which must be
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.
 
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.
Click to expand...

One can write a formula in Excel to find geometric standard deviation as illustrated by another respondent but it would be much easier to use an ExcelGeometric Standard deviation function like this one http://njsxl.njinstruments.com/excel-geometric-standard-deviation-function.html by simply entering the values for data set and have the Excel GEOSTDEV function return the result for geometric standard deviation as shown below

=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.htmlthat is part of njsXL add-in http://njsxl.njinstruments.com/
 
Back
Top