Standard Error of the Estimate

[iperlovsky]

I am looking for help in crafting an excel formula to handle the following
equation.

Se = SQRT(SUM((y-ybar)^2/(n-k-1)))

where, say, y is cells A1:A100, n = 100 (ie, cell count A1:A100).

Any suggestions would be greatly appreciated.

Thanks,

 

[Bernard Liengme]

Your formula = SQRT(SUM((y-ybar)^2/(n-k-1)))
can be implemented in Excel with:

=SQRT(DEVSQ(A1:A100)/(COUNT(A1:A100)-1))
But I am not sure what the k value refers to
My formula gives the standard deviation - the same result that
=STDEV(A1:A100) would yield


Are you familiar with Tools/Data Analysis/Descriptive Statistics?

For the standard error of the mean I use = STDEV(range of
values)/SQRT(number)

best wishes and I hope this is some help

 

[Bernard Liengme]

Sorry I misread this as "standard error of the mead"
You want "standard error of the estimate"
So use my first formula but incorporate K
=SQRT(DEVSQ(A1:A100)/(COUNT(A1:A100)-1-B1))
Here I have used B1 to hold the k value
You could 'hard code' it, say k=5
=SQRT(DEVSQ(A1:A100)/94))