feel that the STANDARD DEVIATION formula on Excel is incorrect !!

Hi Microsoft Office,

The formula for Standard Deviation provided in Excel ( =STDEV(range) )gives
a different answer when compared with the answer got by manually calculating
the Standard Deviation formula provided in the Statistics text books.

E.g.: Numbers - 5, 6, 8 & 9. When calculated using Microsoft Excel =STDEV
(....) we get an answer 1.82574185835055

But when calculated using the Text book methods of calculating of the Std.
Dev. formula, we get 1.58113883008419

Please can you advise why there is a difference in the answers?

Please can you make appropriate changes in the future Microsoft Office
releases giving the breakup of the formulas used? May be in the HELP option.
Because there are millions of excel users who are completely dependent
(unaware) on the formule in excel.

Many thanks,

Ganapati Hegde
Mumbai, INDIA.
+91 98195 58330


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Maybe you should learn statistics a little better. It's been a while since I
took statistics but there are two ways to calculate standard deviation,
depending on wether you are using a sample or the whole population. Try
STDEVP function and you will get the same answer that you are calculating.

Look in the help on these functions to learn what they are doing. STDEV
uses n(n-1) and STDEVP uses n². Which are both correct depending on whether
it is a sample of the population, or the whole population.

"Ganapati Hegde" wrote:

> Hi Microsoft Office,
>
> The formula for Standard Deviation provided in Excel ( =STDEV(range) )gives
> a different answer when compared with the answer got by manually calculating
> the Standard Deviation formula provided in the Statistics text books.
>
> E.g.: Numbers - 5, 6, 8 & 9. When calculated using Microsoft Excel =STDEV
> (....) we get an answer 1.82574185835055
>
> But when calculated using the Text book methods of calculating of the Std.
> Dev. formula, we get 1.58113883008419
>
> Please can you advise why there is a difference in the answers?
>
> Please can you make appropriate changes in the future Microsoft Office
> releases giving the breakup of the formulas used? May be in the HELP option.
> Because there are millions of excel users who are completely dependent
> (unaware) on the formule in excel.
>
> Many thanks,
>
> Ganapati Hegde
> Mumbai, INDIA.
> +91 98195 58330
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

1.82574.... is the sample standard deviation for the data set which is
returned by

=STDEV(5,6,8,9)

1.58113... is the population standard deviation for the data set, which
is returned by

=STDEVP(5,6,8,9)

The formulas that XL uses to calculate both STDEV and STDEVP are already
in XL Help.


In article <[email protected]>,
"Ganapati Hegde" <Ganapati [email protected]> wrote:

> Hi Microsoft Office,
>
> The formula for Standard Deviation provided in Excel ( =STDEV(range) )gives
> a different answer when compared with the answer got by manually calculating
> the Standard Deviation formula provided in the Statistics text books.
>
> E.g.: Numbers - 5, 6, 8 & 9. When calculated using Microsoft Excel =STDEV
> (....) we get an answer 1.82574185835055
>
> But when calculated using the Text book methods of calculating of the Std.
> Dev. formula, we get 1.58113883008419
>
> Please can you advise why there is a difference in the answers?
>
> Please can you make appropriate changes in the future Microsoft Office
> releases giving the breakup of the formulas used? May be in the HELP option.
> Because there are millions of excel users who are completely dependent
> (unaware) on the formule in excel.
>
> Many thanks,
>
> Ganapati Hegde
> Mumbai, INDIA.
> +91 98195 58330
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...=0c737fb5-0d7b
> -4400-b5ba-bacddb8541d9&dg=microsoft.public.excel.worksheet.functions

For all those wondering how the Standard Deviation in Microsoft Excel is used :

The excel function STDEV works correctly as explained in the post by McGimpsy:

1/(1-n).sum( x;i - x;mean) ^2

The confusion however is that the Microsoft Help for the function claims
STDEV works as follows :

1/(1-n).sum( x;i^2 - x;mean^2)

Wich is a bunch of ballocks as the function works just as it should, and
just like statisticians would use it.

Regards,
Robin


--
Robin Vervoorn
"You can't teach a new mouse old clicks.."



"Ganapati Hegde" wrote:

> Hi Microsoft Office,
>
> The formula for Standard Deviation provided in Excel ( =STDEV(range) )gives
> a different answer when compared with the answer got by manually calculating
> the Standard Deviation formula provided in the Statistics text books.
>
> E.g.: Numbers - 5, 6, 8 & 9. When calculated using Microsoft Excel =STDEV
> (....) we get an answer 1.82574185835055
>
> But when calculated using the Text book methods of calculating of the Std.
> Dev. formula, we get 1.58113883008419
>
> Please can you advise why there is a difference in the answers?
>
> Please can you make appropriate changes in the future Microsoft Office
> releases giving the breakup of the formulas used? May be in the HELP option.
> Because there are millions of excel users who are completely dependent
> (unaware) on the formule in excel.
>
> Many thanks,
>
> Ganapati Hegde
> Mumbai, INDIA.
> +91 98195 58330
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions