CODEWITHSUNDEEP
excelmeanstandard-deviation
Eghbal
Eghbal

Reputation: 3783

Summation in EXCEL for calculation of standard deviation

Suppose that i want manually calculate sample standard deviation. As you know we have this formula to do this :

enter image description here

I'm using this function in EXCEL to do this : ( data range : E3:E32 )

=SQRT((E3-GEOMEAN($E$3:$E$32))^2/(COUNT($E$3:$E$32)-1))

But this function calculate standard deviation for every cell (here E3). I need a summation like the above formula.( + this is a manual function to calculate standard deviation using geometric mean). How can I do that in EXCEL ?

Thanks.

Upvotes: 0

Views: 4512

Answers (1)

Brett Wolfington
Brett Wolfington

Reputation: 6627

Here you go. The curly-braces indicate that this must be entered as an array formula (press Ctrl + Shift + Enter to complete the formula in Excel, see here for more information on array formulas):

{=SQRT(SUM(((E3:E32)-GEOMEAN(E3:E32))^2)/(COUNT(E3:E32)-1))}

The following VBA code will also accomplish what you want. Create a new module and add the following:

Function GeoSampleStdDev(rng As Range)
    Dim meanGeo, sum As Double

    meanGeo = WorksheetFunction.geoMean(rng)

    Dim cell As Range
    For Each cell In rng.Cells
        sum = sum + (cell.Value - meanGeo) ^ 2
    Next

    GeoSampleStdDev = Sqr(sum / (rng.Count - 1))
End Function

It can be called in a cell by using the following expression:

=GeoSampleStdDev(E3:E32)

Also, note that the formula for Geometric Standard Deviation differs from what you have above. This is another implementation of the formula, derived from Wikipedia. It is also an array formula:

{=EXP(SQRT((LN((E3:E32)/GEOMEAN(E3:E32))^2)/COUNT(E3:E32)))}

Upvotes: 2

Related Questions