Weighted Standard Deviation in Excel

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.

Video: Weighted Standard Deviation in Excel

Watch on YouTube | Subscribe to EngineerExcel on YouTube

How to Calculate Weighted Standard Deviation

This formula (provided by the United States National Institute for Standards and Technology, or NIST) is used for calculating the 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.

5

5

16

4

13

3

19

2

14

1

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”.

Use Create from Selection to name those arrays by selecting Formulas > Create from Selection under Defined Names.

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:

=SUMPRODUCT(xi,wi)/SUM(wi)

calculate weighted mean average in excel

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.

=COUNTIF(wi,”<>0”)

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 (<>).

count non-zero values in excel

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:

=SQRT(SUMPRODUCT(wi*(xi-xbarw)^2)/(SUM(wi)*(N-1)/N))

weighted standard deviation in excel

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.

Scroll to Top