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.