35
votes
$\begingroup$

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?

$\endgroup$
5
  • $\begingroup$ The formula with (M-1)/M is correct. If you have a doubt, check it by setting all the weights equal to 1, and you will obtain classical formula for unbiased estimate for the standard deviation with (N-1) in the denominator. To whuber: unusual does not mean incorrect. $\endgroup$
    – user56382
    Commented Sep 24, 2014 at 18:10
  • 1
    $\begingroup$ The formula with (M-1)/M is NOT CORRECT. Imagine you add a million points with weights of one trillionth. You don't change your answer at all regardless of what those weights are, but your $(M-1)/M$ term becomes 1? Absolutely NOT! If you care that $(M-1)/M \neq 1$, then you also care that this is just wrong. $\endgroup$
    – Rex Kerr
    Commented Sep 8, 2015 at 16:43
  • $\begingroup$ The highest vote is correct. Please check itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf $\endgroup$
    – Bo Wang
    Commented May 25, 2018 at 5:13
  • $\begingroup$ I wonder why you want the standard deviation here? you only have $4$ numbers! How is that too many numbers? Especially when percentages are more easily explained and understood. $\endgroup$
    – probabilityislogic
    Commented May 25, 2018 at 5:32
  • 3
    $\begingroup$ @probabilityislogic it was a simplified example for keeping the question short. $\endgroup$
    – Yahel
    Commented Jun 11, 2018 at 15:50

6 Answers 6

Reset to default
47
votes
$\begingroup$

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.

$\endgroup$
11
  • 3
    $\begingroup$ @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? $\endgroup$
    – whuber ♦
    Commented Jun 25, 2012 at 14:14
  • 4
    $\begingroup$ @Aaron Weights are not always defined to sum to unity, as exemplified by the weights given in this question! $\endgroup$
    – whuber ♦
    Commented Jan 22, 2013 at 21:36
  • 2
    $\begingroup$ (-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). $\endgroup$
    – whuber ♦
    Commented Jan 22, 2013 at 21:39
  • 2
    $\begingroup$ In light of the added reference (which is not authoritative, but it is a reference) I am removing the downvote. I am not upvoting this answer, though, because calculations show the proposed weighting does not produce an unbiased estimate of anything at all (except when all weights equal $1$). The real difficulty here--which is the fault of the question, not the answer--is that it's not clear what this "weighted standard deviation" is attempting to estimate. Without a definite estimand, there is no justification to introduce an $(M-1)/M$ factor to "reduce bias" (or for any other reason). $\endgroup$
    – whuber ♦
    Commented Jul 31, 2013 at 14:47
  • 1
    $\begingroup$ @Mikhail You are correct that "unusual" and "right" have little to do with one another. However, unusual results do implicitly demand a little more justification because being unusual is one indicator that an error may have been made. Your argument is invalid: although the formula indeed reduces to one for an unbiased estimator when all weights are equal, that does not imply the estimator remains unbiased when unequal weights are used. I am not asserting your conclusion is wrong, but only that so far no valid justification has been offered. $\endgroup$
    – whuber ♦
    Commented Sep 24, 2014 at 20:17
23
votes
$\begingroup$

The formulae are available various places, including Wikipedia.

The key is to notice that it depends on what the weights mean. In particular, you will get different answers if the weights are frequencies (i.e. you are just trying to avoid adding up your whole sum), if the weights are in fact the variance of each measurement, or if they're just some external values you impose on your data.

In your case, it superficially looks like the weights are frequencies but they're not. You generate your data from frequencies, but it's not a simple matter of having 45 records of 3 and 15 records of 4 in your data set. Instead, you need to use the last method. (Actually, all of this is rubbish--you really need to use a more sophisticated model of the process that is generating these numbers! You apparently do not have something that spits out Normally-distributed numbers, so characterizing the system with the standard deviation is not the right thing to do.)

In any case, the formula for variance (from which you calculate standard deviation in the normal way) with "reliability" weights is

$${ \sum {w_i (x_i - x^*)^2} \over {\sum w_i - {\sum w_i^2 \over \sum w_i }} }$$

where $x^* = \sum w_i x_i / \sum w_i$ is the weighted mean.

You don't have an estimate for the weights, which I'm assuming you want to take to be proportional to reliability. Taking percentages the way you are is going to make analysis tricky even if they're generated by a Bernoulli process, because if you get a score of 20 and 0, you have infinite percentage. Weighting by the inverse of the SEM is a common and sometimes optimal thing to do. You should perhaps use a Bayesian estimate or Wilson score interval.

$\endgroup$
8
  • 2
    $\begingroup$ +1. The discussion of the different meanings of weights was what I was looking for in this thread all along. It is an important contribution to all of this site's questions about weighted statistics. (I am a little concerned about the parenthetical remarks concerning normal distributions and standard deviations, though, because they incorrectly suggest that SDs have no use outside a model based on normality.) $\endgroup$
    – whuber ♦
    Commented Sep 8, 2015 at 18:23
  • $\begingroup$ @whuber - Well, central limit theorem to the rescue, of course! But for what the OP was doing, trying to characterize that set of numbers with a mean and standard deviation seems exceedingly inadvisable. And in general, for many uses the standard deviation ends up luring one into a false feeling of understanding. For instance, if the distribution is anything but normal (or a good approximation thereof), relying on the standard deviation will give you a bad idea of the shape of the tails, when it is exactly those tails that you probably most care about in statistical testing. $\endgroup$
    – Rex Kerr
    Commented Sep 8, 2015 at 19:44
  • $\begingroup$ @RexKerr We can hardly blame standard deviation if people place interpretations on it that are undeserved. But let's move away from normality and consider the much broader class of continuous, symmetric unimodal distributions with finite variance (for example). Then between 89 and 100 percent of the distribution lies within two standard deviations. That's often pretty useful to know (and 95% lies pretty much in the middle, so it's never more than about 7% off); with many common distributions, the dropping symmetry aspect doesn't change much (e.g. look at the exponential, for example).... ctd $\endgroup$
    – Glen_b
    Commented Oct 1, 2015 at 23:57
  • $\begingroup$ ctd... -- or if we don't make any of those assumptions, there's always the ordinary Chebyshev bounds which do at least say something about the tails and standard deviation.. $\endgroup$
    – Glen_b
    Commented Oct 1, 2015 at 23:58
  • 1
    $\begingroup$ @Gabriel - Yes, sorry, I was being sloppy. (I figure people can tell which is which by glancing.) I've corrected my description. $\endgroup$
    – Rex Kerr
    Commented Oct 2, 2015 at 1:32
6
votes
$\begingroup$ 
=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

$\endgroup$
2
  • $\begingroup$ Using Ctrl+Shift+ Enter was a gotcha for me, but this seems to work otherwise. $\endgroup$
    – philipkd
    Commented Aug 4, 2016 at 18:40
  • $\begingroup$ How do I calculate the standard error ? $\endgroup$
    – FabioSpaghetti
    Commented May 26, 2020 at 11:01
2
votes
$\begingroup$

If we treat weights like probabilities, then we build them as follows: $$p_i=\frac{v_i}{\sum_iv_i},$$ where $v_i$ - data volume.

Next, obviously the weighted mean is $$\hat\mu=\sum_ip_ix_i,$$ and the variance:$$\hat\sigma^2=\sum_ip_i(x_i-\hat\mu)^2$$

$\endgroup$
1
vote
$\begingroup$

Late in the day I know, but in reference to Whuber's insistance on an authoritative justification for the (M-1)/M term for an unbiased estimate, perhaps Prof. James Kirchner's justification, download currently available at http://seismo.berkeley.edu/~kirchner/Toolkits/Toolkit_12.pdf, which references 

Bevington, P. R., Data Reduction and Error Analysis for the Physical Sciences, 336 pp.,
McGraw-Hill, 1969

will do?

Prof. Kirchner distinguishes between

  1. "Case I" in which some points are more important than others (hence the weighting) but the uncertainties associated with each point are assumed to be the same
  2. "Case II" in which the points are equally important but the uncertainties associated with each point are not the same.

For FabioSpaghetti's comment from yesterday, the above linked paper also shows how to calculate the standard error.

$\endgroup$
0
votes
$\begingroup$ 
Option Explicit

Function wsdv(vals As Range, wates As Range)
Dim i, xV, xW, y As Integer
Dim wi, xi, WgtAvg, N
Dim sumProd, SUMwi

    sumProd = 0
    SUMwi = 0
    N = vals.Count  ' number of values to determine W Standard Deviation
    xV = vals.Column  ' Column number of first value element
    xW = wates.Column  ' Column number of first weight element
    y = vals.Row - 1  ' Row number of the values and weights

    WgtAvg = WorksheetFunction.SumProduct(vals, wates) / WorksheetFunction.Sum(wates)

    For i = 1 To N  ' step through the elements, calculating the sum of values and the sumproduct
        wi = ActiveSheet.Cells(i + y, xW).Value  ' (i+y, xW) is the cell containing the weight element
        SUMwi = SUMwi + wi
        xi = ActiveSheet.Cells(i + y, xV).Value  ' (i+y, xV) is the cell containing the value element
        sumProd = sumProd + wi * (xi - WgtAvg) ^ 2
    Next i

    wsdv = (sumProd / SUMwi * N / (N - 1)) ^ (1 / 2)  ' output of weighted standard deviation

End Function
$\endgroup$
1
  • 2
    $\begingroup$ Welcome to the site, @uswer71015. This seems to be only code. Can you add some text / explanation of how the code works & how it answers the question? $\endgroup$
    – gung - Reinstate Monica
    Commented Mar 12, 2015 at 15:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.