Standard Error Formula

Hi,

I searched the standard error formula in Excel Help and found this:

I tried the formula using this data set:

1

2

3

4

5

and the result is 1.65831. This is wrong, because the standard error should be 0.70711.

I know that the standard error is equal to the sample standard deviation over the square root of the size of the sample. If I use this formula, the result is 0.70711.

Can anyone help with the formula in Excel Help? Thanks in advance.

Answer
Answer
Lester wrote:
I searched the standard error formula in Excel Help and found this:

I tried the formula using this data set:

1

2

3

4

5

and the result is 1.65831. This is wrong, because the standard error should be 0.70711.

I know that the standard error is equal to the sample standard deviation over the square root of the size of the sample. If I use this formula, the result is 0.70711.


There are different statistics called "standard error".


0.70711 is the standard error of the mean of the data {1,2,3,4,5}.


As you say, it is defined as =STDEV(data)/SQRT(n) or, equivalently, =STDEVP(data)/SQRT(n-1).


There is also the standard error of the regression.  See the reference cited in

http://en.wikipedia.org/wiki/Standard_error.


The function STEYX is the "standard error of the predicted y-value for each x in the regression".


You stumbled onto the mathematical formula used to calculate "standard error" error bars in Excel charts.


I find that help page by searching "online" help in Excel 2010.  I don't know if you are quoting it out of context, or if Excel 2013 continues the "tradition" of broken help information, started in Excel 2010. :-(


Note that the formula for the standard error of the mean can be written as:


=SQRT( SUMPRODUCT((data-AVERAGE(data))^2) / ( (n-1)*n ) )


And for a single series, the formula for "standard error" error bars can written as:


=SQRT( SUMPRODUCT((data-0)^2) / ( (n-1)*n ) )

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated May 28, 2020 Views 4,827 Applies to: