Weighted Average Standard Deviation

I'm doing a customer survey where people have responded:

Agree strongly 331
Agree somewhat 100
Neither 50
Disagree somewhat 10
Disagree strongly 5

I want to assign a 1 to 5 score to each response (1=agree strongly) and get
the weighted average standard deviation using just the frequencys above. Is
this possible in Excel? If so, what would the equation be? I saw another
post about a wmean, wsd...but the equation returns a !NAME error.
Please help...Thank you

kthenning wrote:
> I'm doing a customer survey where people have responded:
> Agree strongly 331
> Agree somewhat 100
> Neither 50
> Disagree somewhat 10
> Disagree strongly 5
> I want to assign a 1 to 5 score to each response (1=agree strongly)
> and get the weighted average standard deviation [...].
> Is this possible in Excel?

There might be an easier way, but the following works,
and it straight-forwardly follows the math definitions.

Assume that A1:A5 has the values above, and B1:B5 has
the respective scores. Then the average score (C1) is:

=SUMPRODUCT(A1:A5,B1:B5)/(SUM(A1:A5)-1)

and the variance (C2) of the scores is:

=SUMPRODUCT(A1:A5,(B1:B5-C1)^2)/(SUM(A1:A5)-1)

The standard deviation is simply the square root of
the variance, namely:

=SQRT(C2)

Note: The formulas assume that you want to treat the
responses as samples. For the population average and
variance, remove "-1" in the denominator.

Thank you!!

"joeu2004@hotmail.com" wrote:

> kthenning wrote:
> > I'm doing a customer survey where people have responded:
> > Agree strongly 331
> > Agree somewhat 100
> > Neither 50
> > Disagree somewhat 10
> > Disagree strongly 5
> > I want to assign a 1 to 5 score to each response (1=agree strongly)
> > and get the weighted average standard deviation [...].
> > Is this possible in Excel?
>
> There might be an easier way, but the following works,
> and it straight-forwardly follows the math definitions.
>
> Assume that A1:A5 has the values above, and B1:B5 has
> the respective scores. Then the average score (C1) is:
>
> =SUMPRODUCT(A1:A5,B1:B5)/(SUM(A1:A5)-1)
>
> and the variance (C2) of the scores is:
>
> =SUMPRODUCT(A1:A5,(B1:B5-C1)^2)/(SUM(A1:A5)-1)
>
> The standard deviation is simply the square root of
> the variance, namely:
>
> =SQRT(C2)
>
> Note: The formulas assume that you want to treat the
> responses as samples. For the population average and
> variance, remove "-1" in the denominator.
>
>

joeu2004@hotmail.com wrote:

....

> Assume that A1:A5 has the values above, and B1:B5 has
> the respective scores. Then the average score (C1) is:
>
> =SUMPRODUCT(A1:A5,B1:B5)/(SUM(A1:A5)-1)

I think you meant

=SUMPRODUCT(A1:A5,B1:B5)/SUM(A1:A5)

Jerry

Jerry W. Lewis wrote:
> joeu2004@hotmail.com wrote:
> > Assume that A1:A5 has the values above, and B1:B5 has
> > the respective scores. Then the average score (C1) is:
> > =SUMPRODUCT(A1:A5,B1:B5)/(SUM(A1:A5)-1)
>
> I think you meant
> =SUMPRODUCT(A1:A5,B1:B5)/SUM(A1:A5)

Yes, you are right. Overzealous editing. Only the
variance formula changes for sample v. population
statistics.