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

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:

Elevate Your Engineering With Excel

Advance in Excel with engineering-focused training that equips you with the skills to streamline projects and accelerate your career.

Find Out More

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

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)

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

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

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.