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.
Someone please help.
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)))
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.
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.
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.
Re: using excel to calculate the weighted standard deviation
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.
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.
