If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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. > > 
#4





Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
calculating a weighted average using formula  bob green  Excel Worksheet Functions  1  August 1st 05 10:33 PM 
calculating a weighted average uisng formula  bob green  Excel Worksheet Functions  1  August 1st 05 06:31 AM 
Show weighted average value after filter.  BillC  Excel Worksheet Functions  3  May 3rd 05 04:13 PM 
How to calculate 2 standard deviation?  Li  Excel Worksheet Functions  1  April 12th 05 09:44 PM 
Histrogramms: Calculating average and standard deviation  Jens Eichelbaum  Excel Worksheet Functions  2  November 23rd 04 02:10 AM 