Standard Deviation from Goal, NOT Mean

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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?
 
VERY Helpful!! Thanks.

Bernard Liengme said:
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?
Click to expand...
 
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?
Click to expand...

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.
 
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
 
Back
Top