Standard Deviation from Goal, NOT Mean

V

Victoria

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
 
B

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?
 
V

Victoria

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?
 
J

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.
 
J

Jerry W. Lewis

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top