# How do I calculate a weighted standard deviation? In Excel?

So, I have a data set of percentages like so:

100   /   10000   = 1% (0.01)
2     /     5     = 40% (0.4)
4     /     3     = 133% (1.3)
1000  /   2000    = 50% (0.5)


I want to find the standard deviation of the percentages, but weighted for their data volume. ie, the first and last data points should dominate the calculation.

How do I do that? And is there a simple way to do it in Excel?

-

$$\sqrt{ \frac{ \sum_{i=1}^N w_i (x_i - \bar{x}^*)^2 }{ \frac{(M-1)}{M} \sum_{i=1}^N w_i } },$$

where

$N$ is the number of observations.

$M$ is the number of nonzero weights.

$w_i$ are the weights

$x_i$ are the observations.

$\bar{x}^*$ is the weighted mean.

Remember that the formula for weighted mean is:

$$\bar{x}^* = \frac{\sum_{i=1}^N w_i x_i}{\sum_{i=1}^N w_i}.$$

Use the appropriate weights to get the desired result. In your case I would suggest to use $\frac{\mbox{Number of cases in segment}}{\mbox{Total number of cases}}$.

To do this in Excel, you need to calculate the weighted mean first. Then calculate the $(x_i - \bar{x}^*)^2$ in a separate column. The rest must be very easy.

-
Thanks for your post. But I think there may be a mistake in the weighted mean expression. Shouldn't it be divided with the sum of the weights ? –  user12195 Jun 25 '12 at 13:40
@Gilles, you're right. deps_stats, the fraction $(M-1)/M$ in the SD is unusual. Do you have a citation for this formula or can you at least explain the reason for including that term? –  whuber Jun 25 '12 at 14:14
the sum of all weights is BY DEFINITION = 1, so what is the point of including that in the divisor term? –  user19943 Jan 22 at 21:28
@Aaron Weights are not always defined to sum to unity, as exemplified by the weights given in this question! –  whuber Jan 22 at 21:36
(-1) I am downvoting this answer because no justification or reference for the $(M-1)/M$ term has been provided (and I'm pretty sure it does not make the estimate of the variance unbiased, which would be its apparent motivation). –  whuber Jan 22 at 21:39
show 1 more comment
=SQRT(SUM(G7:G16*(H7:H16-(SUMPRODUCT(G7:G16,H7:H16)/SUM(G7:G16)))^2)/
((COUNTIFS(G7:G16,"<>0")-1)/COUNTIFS(G7:G16,"<>0")*SUM(G7:G16)))


Column G are weights, Column H are values

-