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

Question

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?

Answer 1

The formula for weighted standard deviation is:

$$ \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.