Standard Deviation from Goal, NOT Mean

[Guest]

I've been asked to determine what the standard deviation is from a goal for
52 values. I know that technically speaking, standard deviation is from the
mean however I need to calculate it from the goal. Is there a formula or
string of formulas I can use?
Thank you.
Victoria

 

[Bernard Liengme]

If I have numbers 3,4,5,6 and a goal of 4
I need to find (3-4)^2, (4-4)^2, (5-4)^2, and (6-4)^2
Then I sum these values
Next I divide by 3 (there are four numbers, so I divide by N-1 (this is for
a sample std dev, for a population deviation divide by N)
Finally I find the square root of the result.
A1 4 (goal)
A2 3 B2 =(A2-$A$1)^2
A3 4 B3 =(A3-$A$1)^2
.....
=SQRT(SUM(B2:B54)/(COUNT(B4:B54)-1))

If you want to avoid the B column, I expect we could come up with a
SUMPRODUCT formula.
Any help?

 

[Guest]

VERY Helpful!! Thanks.

If I have numbers 3,4,5,6 and a goal of 4
I need to find (3-4)^2, (4-4)^2, (5-4)^2, and (6-4)^2
Then I sum these values
Next I divide by 3 (there are four numbers, so I divide by N-1 (this is for
a sample std dev, for a population deviation divide by N)
Finally I find the square root of the result.
A1 4 (goal)
A2 3 B2 =(A2-$A$1)^2
A3 4 B3 =(A3-$A$1)^2
.....
=SQRT(SUM(B2:B54)/(COUNT(B4:B54)-1))

If you want to avoid the B column, I expect we could come up with a
SUMPRODUCT formula.
Any help?

 

[joeu2004]

I've been asked to determine what the standard deviation is from a goal for
52 values. I know that technically speaking, standard deviation is from the
mean however I need to calculate it from the goal. Is there a formula or
string of formulas I can use?

You are correct that Excel's STDEV and STDEVP functions only compute
the standard deviation from the mean. But the standard deviation per
se is merely a formula -- a measure of dispersion from any "center",
not just the mean. So if you look at the formula on the STDEVP help
page and substitute "g" (goal) where you see "x-hat" (mean), that is
the formula to use for any standard deviation.

If your 52 values are in A1:A52 and your goal is in B1, the following
is one way to compute the standard deviation:

=sqrt(sumproduct((A1:A52-B1)^2) / count(A1:A52))

Of course, you could simply use the number 52 instead of
count(A1:A52). I prefer the latter because it adjusts the count
automatically if I insert some more numbers into the range. I use
SUMPRODUCT simply to avoid using an array formula if I had used SUM.

 

[Guest]

In standard terminology, this is the "root mean squared error" (RMSE)
http://en.wikipedia.org/wiki/Mean_squared_error

A useful way to think about and calculate this quantity is
=SQRT(VARP(data)+(AVERAGE(data)-goal)^2)

Jerry

 

[Guest]

I think you meant
=SQRT(SUM(B2:B54)/COUNT(B4:B54)

The goal is presumably known external to the data, and hence you do not lose
a degree of freedom for estimating the mean.

Jerry