# using excel to calculate the weighted standard deviation

• Dec 4th, 2012, 01:53 PM
David Wayne
using excel to calculate the weighted standard deviation
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.

• Dec 4th, 2012, 02:25 PM
TheVillageIdiot
Re: using excel to calculate the weighted standard deviation
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)))
• Dec 4th, 2012, 03:38 PM
David Wayne
Re: using excel to calculate the weighted standard deviation
Quote:

Originally Posted by 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)))

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

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.
• Dec 4th, 2012, 03:58 PM
shg
Re: using excel to calculate the weighted standard deviation
Quote:

However, I know the answer for these data should be 2.61.
You sure about that? I think it's 4.2.

Code:

```      --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/softw...2/weightsd.pdf.
• Dec 5th, 2012, 12:11 PM
David Wayne
Re: using excel to calculate the weighted standard deviation
Quote:

Originally Posted by shg
You sure about that? I think it's 4.2.

Code:

```      --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/softw...2/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.
• Dec 5th, 2012, 01:59 PM
shg
Re: using excel to calculate the weighted standard deviation
Quote:

Thank you
You're welcome.
Quote:

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.
• Dec 6th, 2012, 12:46 AM
shg
Re: using excel to calculate the weighted standard deviation
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.