In this post, you’ll learn how to calculate a weighted standard deviation in Excel. A weighted standard deviation calculates the spread in a data set where not all the observations have the same weight.
VideoSubscribe to EngineerExcel on YouTube
How to Calculate Weighted Standard Deviation
- w is the weight of each observation,
- x is the value of the observation,
- xbarw is the weighted average, and
- N’ is the number of non-zero observations
To get started, we need two sets of data. The first column of data contains the samples, and the second column of data contains the weight of each of those samples.
The data is in columns B and C on the spreadsheet.
Give the column containing the samples the name “xi” and the column containing the weights the name “wi”.
Make sure “Top Row” is checked and click OK.
Now, the array of values underneath xi has the name xi:
The array of values underneath wi has the name wi:
Weighted Average in Excel
Next, calculate the weighted average of the samples.
We’ll call that xbarw, and the formula for the weighted average is:
The SUMPRODUCT function multiplies each element in xi by the corresponding element in wi, and then sums the resulting values to return a single value.
The resulting weighted average or weighted mean is 12.
Count Non-Zero Values in Excel
Next, count the number of non-zero weights using the COUNTIF function.
The argument “<>0” is enclosed in double quotations because Excel requires a string as an input.
The way to represent “not equal” in Excel (or VBA) is a less than sign followed immediately by a greater than sign (<>).
The total number of non-zero weights is 5.
Calculate Weighted Standard Deviation in Excel
Finally, calculate the weighted standard deviation by using Excel functions to create the formula from above.
Using Excel functions, the formula is:
The resulting weighted standard deviation is approximately 5.9.
What is a Weighted Standard Deviation?
The standard deviation provides information about the distribution between observations in a sample or population.
In statistics, a weight is a value given to increase or decrease the importance of a sample. Typically, weighting is applied when calculating a mean, but it can also be used in a calculation of standard deviation.
The concept of a weighted standard deviation can be difficult to grasp. Essentially, it calculates the spread in a data set where not all the observations have the same weight.