Excel and standard deviation

[JC]

I've got a table full of numbers in Excel and i'm trying to calculate standard deviation for a load of columns.

Firstly i tried =STDEV(A1, B1...) but that came up with a different answer to my calculator. Excel uses the forumla:

√nΣx^2 - (Σx)^2 / n(n-1)
Now when i think of standard deviation i think of the formula √Σ(x-xbar)^2 / n-1

So i looked a bit deeper into Excel and it has 3 different standard deviations - STDEV, STDEVP, STDEVPA and STDEVA.

STDEVP and STDEVPA seem to give me the same answer as my calculator but both use the forumula √nΣx^2 - (Σx)^2 / n^2

Whats the difference between all these? Which of STEVP and STDEVPA should i use if there is any difference in them seeing as they both use the same formula?


This isnt programming and it isnt hardware so bollocks to anyone that complains but feel free to move it .

[Stew]

One is for sample deviation (n-1) and the other is for population standard deviation.

[queball]

Σ(x-xbar)^2
= Σx^2 - 2Σx*xbar + Σxbar^2
= Σx^2 - 2(Σx)^2 / n + n(Σx/n)^2
= Σx^2 - (Σx)^2 / n

So the only difference is whether you divide by n-1 or n.
Since you're calculating the sample mean, you lose a degree of freedom, so n-1 is more correct.

I don't see a reason why you'd ever want √nΣx^2 - (Σx)^2 / n^2, but you would divide by n rather than n-1 if you already knew the mean of the distribution: √Σ(x-m)^2 / n


The difference between STDEVP and STDEVPA is just technical. http://support.microsoft.com/default...product=xl2003

[NEWSBOT3]

yay, queball is alive

(someone on irc was saying you'd not been around for a bit)