Thank you!!
" 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.
|