standard deviation

M

Munz

Does the standard deviation formula in MS Excel calculate with arithmetic or
geometric mean? How could I make it calculating with the geometric mean?

Many thanks for your help!!
 
H

Harlan Grove

Does the standard deviation formula in MS Excel calculate with arithmetic or
geometric mean? How could I make it calculating with the geometric mean?

It calculates with arithmetic mean (as it should).

Either you mean something like

=EXP(DEVSQ(LN(values))/(COUNT(values)-1))

or

=SUM((values-GEOMEAN(values))^2)/(COUNT(values)-1)

but it's unclear which.
 
H

Harlan Grove

OP responded via e-mail: "Thanks a lot for your help. I am talking about the
second possibility you mentioned. However, as far as I know it is no
possible to enter this directly into MS Excel…do you have any idea how to
write a formula in order to calculate it?"

Harlan Grove said:
=SUM((values-GEOMEAN(values))^2)/(COUNT(values)-1)
....

It's possible to enter this exact formula for a single cell. Did you try?
You may have to replace the token 'values' with a range address or an array
reference, and you do have to enter it as an array formula, holding down
[Ctrl] and [Shift] keys before pressing the [Enter] key, but it most
certainly does work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top