This article explains how to calculate weighted average in Excel. Weighted average is a method of finding an average that accounts for the different weights or importance of the values. For instance, if you have a list of grades and their corresponding percentages, weighted average can be used to find the overall grade.
To calculate weighted average in Excel, one needs to use the SUMPRODUCT function and the SUM function. The following steps describe the process:
- Create a table with the values and their weights. For example, if one has grades and percentages, put them in columns A and B.
- In an empty cell, enter
=SUMPRODUCT(A2:A6,B2:B6)and press Enter. This will multiply each value by its weight and sum them up. - In another empty cell, enter
=SUM(B2:B6)and press Enter. This will sum up all the weights. - Divide the result of step 2 by the result of step 3. For example, if one entered the formulas in cells C2 and C3, enter
=C2/C3and press Enter. This will yield the weighted average.
This is how you can calculate weighted average in Excel. This method can be applied to any kind of data that has different weights or importance. However, you should ensure that the weights add up to 100% or 1.