Home 
Search 
Today's Posts 
#1




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 
#2




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 straightforwardly 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:B5C1)^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. 
#3




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 straightforwardly 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:B5C1)^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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
calculating a weighted average using formula  Excel Worksheet Functions  
calculating a weighted average uisng formula  Excel Worksheet Functions  
Show weighted average value after filter.  Excel Worksheet Functions  
How to calculate 2 standard deviation?  Excel Worksheet Functions  
Histrogramms: Calculating average and standard deviation  Excel Worksheet Functions 