Statistical
Population weighted standard deviation with frequency or probability weights
Calculate weighted variance and weighted standard deviation around the weighted mean in one LET + SUMPRODUCT formula.
Measure dispersion around the weighted mean, not simple AVERAGE
STDEV.P gives each row equal weight. Frequency counts or scenario probabilities require the weighted mean and weighted squared deviations shown here.
First compute the weighted mean wm = SUMPRODUCT(Weights, Values) / SUM(Weights), then weight each squared deviation (Values - wm)^2 by its weight and take SQRT.
| Scenario (A) | Score (B) | ProbWeight (C) | Weighted Sq Deviation |
|---|---|---|---|
| Bear | 100 | 0.2 | 0.2 * (100 - 101)^2 = 0.2 |
| Base | 120 | 0.3 | 0.3 * (120 - 101)^2 = 108.3 |
| Bull | 90 | 0.5 | 0.5 * (90 - 101)^2 = 60.5 |
Population-weighted standard deviation and variance
Weighted mean = 101; weighted variance = 0.2 + 108.3 + 60.5 = 169; weighted standard deviation = SQRT(169) = 13.
=LET(wm, SUMPRODUCT(C2:C4, B2:B4) / SUM(C2:C4), SQRT(SUMPRODUCT(C2:C4, (B2:B4 - wm)^2) / SUM(C2:C4)))Weighted variance (before square root)
Dividing by SUM(C2:C4) works whether weights are probabilities summing to 1.0 or integer counts summing to N.
=LET(wm, SUMPRODUCT(C2:C4, B2:B4) / SUM(C2:C4), SUMPRODUCT(C2:C4, (B2:B4 - wm)^2) / SUM(C2:C4))Inputs and interpretation
Require complete numeric values, nonnegative weights and a positive weight total. This is population dispersion, not an unbiased sample estimator; sample corrections depend on the meaning of the weights. Weighting individual asset returns by allocations does not calculate portfolio volatility, which also depends on covariances.
Try the example
Use the original values or change them to test your understanding.