Weighted Standard Deviation – Excel and Google Sheets

This tutorial will demonstrate how to calculate weighted standard deviation in Excel and Google Sheets.

Weighted Standard Deviation in Excel

Standard Deviation measure the spread of a data distribution. It measures the average distance of each data point from the mean of the dataset.Weighted Standard Deviation measures the spread of a data distribution from the mean where some of the data are more significant than others. The weighted standard deviation is calculated based on the weighted mean and it attaches more importance to data that have more weight than to data with less weight.

Weighted Standard Deviation Formula

The weighted standard deviation is calculated using the formula:

Weighted Standard Deviation Formula

where wi are the weights corresponding to each individual observation;

xi are the individual observations;

xw is the weighted mean;

M is the number of non-zero weights;

Σ is a Greek letter called sigma which represents ‘sum’; and

n is the sample size (the number of observations).

How to Calculate Weighted Standard Deviation in Excel

Background: Hilary is taking a college statistics course. The course is made up of 15 weeks of activities consisting of 11 homework, 3 quizzes, and a final exam. The final exam, the quizzes, and the homework contribute 50%, 30%, and 20% to the final grade respectively.  The table below shows the scores (out of 100) Hilary earned in each of the activities. Calculate the weighted standard deviation of Hilary’s scores.

Weighted Standard Deviation Example in Excel Step1

First, add the weights.

Weighted Standard Deviation Example in Excel Step2

Next, calculate the Weighted Mean using the SUMPRODUCT Function.

Calculate the Weighted Mean using the Sumproduct in Excel

Next, calculate the Squared Deviation of the scores from the Weighted Mean.

Example Step4 Calculate Squared Deviation in Excel

Note: Double-Click the bottom right corner of the cell to fill down the data to the rest of the column.

The complete Squared Deviation column is shown below.

Example Step4 Complete Squared Deviation Column in Excel

 

Next, obtain M, the number of non-zero weights.

Example Step5 Obtain M in Excel

Note: The symbol  represents the ‘not equal to’ operator in Excel.

 

Finally, calculate the Weighed Standard Deviation using the SUMPRODUCT Function as shown below:

Example Step6 Calculate the Weighed Standard Deviation in Excel

 

Weighted Standard Deviation in Google Sheets

The Weighted Standard Deviation can be calculated in Google Sheets in the exact same way as it is calculated in Excel as shown in the picture below.

Weighted Standard Deviation in Google Sheets