Standard Deviation without calculating HPR

[Raphael Excel]

I would like to calculate Geometric and Arithmetic standard deviation of a sample without calculating intermediate HPR returns.

ie: data set
200
300
400
500

I cannot compute intermediate HPR values (300/200)-1, (400/300)-1 etc.

I need to calculate standard deviation of above sample by using only one cell for Arithmetic standard deviation and only one cell for Geometric standard deviation.

I think there's a way using an array function control-shift enter but not sure how.

 

[offthelip]

Is there some reason you can't use one of the EXCEL Standard deviation functions: STDEV, STDEV.P STDEV.A etc

 

[joeu2004]

With your data in A1:A4....

For the arithmetic std dev, array-enter (press ctrl+shift+Enter instead of just Enter) the following:

=STDEV(A2:A4 / A1:A3 - 1)

That is the estimated std dev based on a sample. Use STDEVP for the exact std dev.

For the geometric std dev, we calculate the arithmetic std dev of the log values. Array-enter the following:

=EXP(STDEV(LN(A2:A4 / A1:A3))) - 1
or
=10^STDEV(LOG(A2:A4 / A1:A3))) - 1

Note: It is debatable whether to just calculate the log std dev -- that is, just STDEV(LN...) or STDEV(LOG...). I believe we should only use the geometric std dev with the log distribution, assuming a log normal distribution.

 

[joeu2004]

PS....

It is debatable whether to just calculate the log std dev -- that is, just STDEV(LN...) or STDEV(LOG...). I believe we should only use the geometric std dev with the log distribution, assuming a log normal distribution.

But in that case, you would also calculate the log mean, not the geometric mean. (Refer to your other thread.) In other words, array-enter the following:

=AVERAGE(LN(A2:A4 / A1:A3))
or
=AVERAGE(LOG(A2:A4 / A1:A3))

 

[joeu2004]

PPS (with apologies for the incessant postings)....

It is debatable whether to just calculate the log std dev -- that is, just STDEV(LN...) or STDEV(LOG...). I believe we should only use the geometric std dev with the log distribution, assuming a log normal distribution.

But in that case, you would also calculate the log mean, not the geometric mean. (Refer to your other thread.) In other words, array-enter the following:
=AVERAGE(LN(A2:A4 / A1:A3))
or
=AVERAGE(LOG(A2:A4 / A1:A3))

Well, duh.... Normally-enter (just press Enter as usual):

=LN((A4/A1)^(1/3))
or
=LOG((A4/A1)^(1/3))

 

[Raphael Excel]

PPS (with apologies for the incessant postings)....



Well, duh.... Normally-enter (just press Enter as usual):

=LN((A4/A1)^(1/3))
or
=LOG((A4/A1)^(1/3))

thank you for all the help