using excel to calculate the weighted standard deviation

[David Wayne]

Does anyone know the formula for computing the weighted standard deviation? I was able to calculate the weighted average (16.4) but have been unsuccessful in computing the weighted SD. I conducted numerous searches and have not been able to find a posted formula that actually works.
My data are as follows:

Year Shootings Weight*
2003 4 1
2004 17 2
2005 14 3
2006 19 4
2007 18 5

*The reason I set up the weights the way I did is because I want to give more weight to the more recent years of shootings when comparing them to other years such as 2008, 2009, 2010 and so on.

Someone please help.

 

[TheVillageIdiot]

With your data in A2:C6 (headers in row 1), try the following:

Weighted Average (in cell G2) =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

Variance column (I used D2 and copied down to D6) =($B2-$G$2)^2

Weighted Standard Deviation (Sample)
=SQRT(SUMPRODUCT(D2:D6,C2:C6)/(SUM(C2:C6)-1))

Weighted Standard Deviation (Population)
=SQRT(SUMPRODUCT(D2:D6,C2:C6)/(SUM(C2:C6)))

 

[David Wayne]

With your data in A2:C6 (headers in row 1), try the following:

Weighted Average (in cell G2) =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

Variance column (I used D2 and copied down to D6) =($B2-$G$2)^2

Weighted Standard Deviation (Sample)
=SQRT(SUMPRODUCT(D2:D6,C2:C6)/(SUM(C2:C6)-1))

Weighted Standard Deviation (Population)
=SQRT(SUMPRODUCT(D2:D6,C2:C6)/(SUM(C2:C6)))

===============================================================================================================

Thank you for your reply. However, I know the answer for these data should be 2.61. I just dont know how they got that answer. I plugged in the formulas you provided and came up with 3.89 for the weighted SD.

 

[shg]

However, I know the answer for these data should be 2.61.

You sure about that? I think it's 4.2.

       --A--- B- --C-- ----------------------------D-----------------------------
   1    Year  xi  wi                                                             
   2     2003  4     1                                                           
   3     2004 17     2                                                           
   4     2005 14     3                                                           
   5     2006 19     4                                                           
   6     2007 18     5                                                           
   7                                                                             
   8   WgtAvg     16.4 C8: =SUMPRODUCT(xi, wi)/SUM(wi)                           
   9   N             5 C9: =COUNT(xi)                                            
  10   SDw       4.199 C10: =SQRT(SUMPRODUCT(wi*(xi-WgtAvg)^2)/SUM(wi) * N/(N-1))

See http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf.

 

[David Wayne]

You sure about that? I think it's 4.2.

       --A--- B- --C-- ----------------------------D-----------------------------
   1    Year  xi  wi                                                             
   2     2003  4     1                                                           
   3     2004 17     2                                                           
   4     2005 14     3                                                           
   5     2006 19     4                                                           
   6     2007 18     5                                                           
   7                                                                             
   8   WgtAvg     16.4 C8: =SUMPRODUCT(xi, wi)/SUM(wi)                           
   9   N             5 C9: =COUNT(xi)                                            
  10   SDw       4.199 C10: =SQRT(SUMPRODUCT(wi*(xi-WgtAvg)^2)/SUM(wi) * N/(N-1))

See http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf.

================================================================================================================================


Thank you very much for your reply and assistance. The only issue I'm wondering about is based on the weighted SD attachment that you appended, shouldnt the last part of the formula be: SUM(wi)*(N-1)/N

Thanks again for your help as it was very useful.

 

[shg]

Thank you

You're welcome.

shouldnt the last part of the formula be: SUM(wi)*(N-1)/N

No; the unbiased estimator for the sample gives a larger SD than for the population.

 

[shg]

BTW, if the wights were actually frequencies of observations (versus random old weights), the other formula ...

=SQRT(SUMPRODUCT(D2:D6,C2:C6)/(SUM(C2:C6)-1))

would give the same result.