Weighted Average in Excel

The generic weighted average formula is given as follows:

The letter “x” represents the numeric value, while “w” represents the relative weight in percentage (%). The weights need not always be in percentages. Moreover, the weights may or may not add up to 100%.

The SUMPRODUCT and SUM formula for calculating the weighted average is stated as follows:

“=SUMPRODUCT(numerical values,weights)/SUM(weights)”

The “numerical values” and “weights” can be cell ranges (arrays) or named ranges. Both these arguments should be supplied for calculating the weighted average.

Note 1: If all the numeric values are assigned equal weights, the weighted average in excel is equal to the simple average. In a simple average, all the numerical values of the dataset are treated equally.

Note 2: Both the preceding formulas can be used in Excel to calculate the weighted average. In the second formula, the SUMPRODUCT function shortens an otherwise lengthy numerator of the generic formula.

Moreover, use the denominator of the two preceding formulas when weights do not total up to 100%. In cases where weights add up to 100%, the division by the sum of weights is not required. This is because the sum of weights (or the denominator) equals to 1 in such cases.

Let us take a look at the procedure of calculating the weighted average in Excel. The entire procedure is explained step-by-step with the help of images. Every step is demonstrated by its corresponding image.

Example #1–Weighted Average Using the Generic Formula

The following image shows certain numeric values in column A and weights in column B. We want to calculate the weighted average for the given dataset.

Use the generic weighted average formula (first formula under the heading “The Weighted Average Formulas of Excel” of this article).

The steps for calculating the weighted average with the generic excel formula are listed as follows:

Step 1: In any cell, say B8, enter the following formula.

“=A2*B2+A3*B3+A4*B4+A5*B5+A6*B6)/15”

Step 2: Press the “Enter” key and the output appears in cell B8. So, the weighted average is 38.33.

Explanation: In the formula entered in step 1, we have multiplied the individual entries (of columns A and B) and summed up the products. The resulting output is then divided by 15, which is the sum of all the weights (of column B). Hence, the final output is 38.33.

Since we did not use the SUMPRODUCT function (in step 1), we performed the multiplication and addition operations on columns A and B manually. However, had we used the SUMPRODUCT and SUM functions, the output would have been the same as obtained in the preceding step (step 2).

Example #2–Weighted Average to Evaluate the Performance of a Student

The following image shows the marks obtained (in column B) by a student in six subjects (in column A). These subjects are Mathematics (maths), Physics (phy), Chemistry (chem), Biology (bio), Computers (comp), and English (eng).

Further, the maximum marks of each subject are 100. Every subject has been assigned a weight (in column C) depending on its importance in the education system.

Perform the following tasks:

• Calculate the weighted average by using the SUMPRODUCT and SUM functions of Excel.
• Assess the performance of the student and state how the weighted average can be improved.

The steps to perform the given tasks are listed as follows:

Step 1: Enter the following formula in cell B9.

“=SUMPRODUCT(B2:B7,C2:C7)”

The same is shown in the following image.

Step 2: Press the “Enter” key. The output of the SUMPRODUCT function is shown in the following image. Next, in cell B10, enter the following SUM formula.

“=SUM(C2:C7)”

Step 3: Press the “Enter” key. The output of the SUM function is 26, as shown in the following image. Further, enter the following division formula in cell B11.

“=B9/B10”

Step 4: Press the “Enter” key. The final output appears in cell B11. So, the weighted average is 83.846.

Explanation: The SUMPRODUCT function (step 1) multiplies the values of column B with the corresponding values of column C. It then adds the resulting products and returns 2180.

Likewise, the SUM function totals the weights and returns 26. So, 2180 divided by 26 equals to 83.846, which is the weighted average.

Assessment of performance: By looking at the weighted average (83.846), one can say that the student has performed fairly well in almost all the subjects. The score in Biology (60) is the lowest compared to the marks obtained in other subjects. However, this low score is offset by the high scores and higher weights of the subjects Mathematics, Physics, and Chemistry.

To improve the weighted average, the student must focus more on subjects having the highest weights (5). In addition, any progress in the marks of Biology can also increase the weighted average. This is because the weight of Biology (4) too is on the higher side.

Example #3–Weighted Average to Evaluate the Performance of an Employee

The following image shows the scores (out of 100 in column B) of an employee on five factors, namely, productivity, accuracy, teamwork, login hours, and attendance. Each factor has been assigned a weight (in column C) by the organization.

Perform the following tasks:

• Calculate the weighted average in Excel by using the SUMPRODUCT and SUM functions of Excel.
• Evaluate the performance of the employee and suggest the way the weighted average can improve.
• Show the impact on the weighted average by interchanging the score (or value) of the factor having the maximum weight with the one that has the minimum weight.

The steps to perform the given tasks are listed as follows:

Step 1: Enter the following SUMPRODUCT formula in cell B8.

 “=SUMPRODUCT(B2:B6,C2:C6)”

Step 2: Press the “Enter” key and the output is 1225, as shown in the succeeding image. Next, in cell B9, enter the following SUM formula.

“=SUM(C2:C6)”

Step 3: Press the “Enter” key and the output of the SUM function is 15. Divide the output of the SUMPRODUCT function by that of the SUM function. For this, enter the following formula in cell B10:

“=B8/B9”

The same is shown in the following image.

Step 4: Press the “Enter” key. The weighted average is 81.66, as shown in the following image.

Explanation: The SUMPRODUCT function is applied on ranges B2:B6 and C2:C6 of the given dataset. So, the corresponding numbers of columns B and C are multiplied and the resulting products are added. This returns 1225.

The SUM function returns the sum of the weights (of column C), which is 15. The weighted average is obtained by dividing 1225 by 15. Hence, the weighted average is 81.66.

Assessment of performance: The performance of the employee is quite satisfactory on all factors. From all the given factors, attendance is the best scoring factor but has the least weight (1). Likewise, accuracy is the least scoring factor but has a high weight (4).

In order to improve the weighted average, the employee must focus more on productivity. This is because productivity is allotted the maximum weight (5). So, as productivity increases, the weighted average of the employee will increase considerably.

Likewise, there is a lot of scope for improvement on the accuracy factor. Any increase in the score of this factor can also help increase the weighted average to a great extent.

Impact on the weighted average: To study the impact on the weighted average, interchange the values (or scores) of productivity and attendance having the maximum and minimum weight respectively. The weights of column C remain the same (as given at the beginning of this example). This is shown in the succeeding image.

The weighted average has increased from 81.66 to 86.46. So, an increase of 22.5% in the value of productivity has increased the weighted average by 5.8%. This shows that a significant increase in the high-weight value has a fair increase on the weighted average.

Moreover, it can be said that productivity impacts the overall performance of the employee to the greatest extent. This is due to the allotment of the highest weight (5) to this factor.

Note: The higher the weight, the more the impact on the weighted average. Lesser the weight, lower the impact on the weighted average.