Hello,

I have a macro that filters results based on countries in Column A. Column B holds means and Column C holds weights.

My goal is to calculate the weighted standard deviation of Column B based on what is filtered in Column A using the weights in Column C.


I am hoping someone can verify if what I have below is correct or perhaps offer a better solution. I'm sorry to paste it here, but I am getting error messages when I try to upload an excel document.

The formula for weighted standard deviation I am using is here:
http://www.itl.nist.gov/div898/softw...2/weightsd.pdf


The data set I am testing on is below, semicolon-delimited (the final calculated WSD will appear in B11, if you paste this in starting at A1):

country;x;w;x*w;sdwi^2
US;22;4000;=B2*C2;=C2*(B2-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C2/SUBTOTAL(2,($B$2:$B$9)));84.8115050934084
US;23;3243;=B3*C3;=C3*(B3-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C3/SUBTOTAL(2,($B$2:$B$9)));66.2640159323057
CA;24;2353;=B4*C4;=C4*(B4-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C4/SUBTOTAL(2,($B$2:$B$9)));50.0022410569172
ITALY;23;504;=B5*C5;=C5*(B5-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C5/SUBTOTAL(2,($B$2:$B$9)));66.2640159323057
RUSSIA;33;32230;=B6*C6;=C6*(B6-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C6/SUBTOTAL(2,($B$2:$B$9)));6.50341003556387
RUSSIA;26;11;=B7*C7;=C7*(B7-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C7/SUBTOTAL(2,($B$2:$B$9)));24.3358341632831
GHANA;32;2340;=B8*C8;=C8*(B8-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C8/SUBTOTAL(2,($B$2:$B$9)));2.19375633952376
GHANA;24;230;=B9*C9;=C9*(B9-SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9))^2/((SUBTOTAL(2,($C$2:$C$9))-1)*C9/SUBTOTAL(2,($B$2:$B$9)));50.0022410569172
w st.dev.;=SQRT(SUBTOTAL(9,F2:F9));



To explain my thinking:
1.) The macro I currently run filters my data based on Column A. I need the WSD to re-calculate every time the filter is changed. Because of this I am using SUBTOTAL
2.) Creating Column D and adding --SUBTOTAL(9,$D$2:$D$9)/SUBTOTAL(9,$C$2:$C$9)-- is how I am calculating the weighted mean. I don't know a better way to do this, but perhaps there is one.
3.) I paste values for the results into column F because --=SQRT(SUBTOTAL(9,F2:F9))-- returns "0" if I run the subtotal over the formulas in column E. Not sure why this is happening, not really a big deal.

Thanks for the help!