How to calculate weighted average in Excel?

Weighted average is a calculation, which includes various degree of importance of values. From this article, you will learn how to calculate a weighted average in Excel by using the SUMPRODUCT and SUM formulas.


In one of my the previous articles, I have presented basic methods of calculating the arithmetic average, however, Excel unfortunately does not provide any special function for calculating weighted average. In such situation, we can use other available functions and one of the best for this purpose is SUMPRODUCT.

The SUMPRODUCT function is used to multiply values entered to data ranges and then sums them up. Syntax of this function is:

=SUMPRODUCT(array1; [array2]; [array3];…)

Array 1,2,3…. – these are arguments of the function and they define ranges of data that should be multiplied by each other and then results of multiplications are added.

How works SUMPRODUCT function?

weighted average

In the example above, the arguments for the function are data ranges B2 to B7 and C2 to C7. The SUMPRODUCT function multiplies values ​​from B2-B7 by the values ​​from C2-C7, and then sums up the results (B2 * C2 + B3 * C3 + B4 * C4, etc.).

Important note:
Array arguments used in the SUMPRODUCT function must have the same size. In the situation, that one of the used tables is bigger or smaller than the other ones, the function will return the #VALUE! error, which indicates that formula has been typed incorrectly:

#VALUE! error

Examples for calculating weighted average

Below, I will show you two practical examples, in which you can use the function SUMPRODUCT and SUM to calculate the weighted average in Excel.

Example 1 – weights are summing up to 100%

A student during the semester received some grades for individual tasks, which have specific percentage weight assigned (the sum of weights is 100%):

weighted average

Based on this information, we want to calculate the final grade, but due to different weights for individual grades, we can’t use the arithmetic average. By using the SUMPRODUCT function, we can calculate the sum of individual grades multiplied by their weights, so we get a weighted average:

weighted average

Each value from column B was multiplied by the corresponding weight in column C, and then the results were added. Obtained result (3,75) is a weighted average, so the student’s grade should be 4 (rounded to the whole number).

The following picture shows that an identical result could also be received if we manually multiply each grade by the corresponding weight (results in column D, the formulas used in column E have been used) and add them:

weighted average

Example 2 – weights are not summing up to 100% or are not percentage values

The example described above was quite simple, as the weights were percentage values and they summed up to 100%. However, what should we do, if the weights do not sum up to 100% or are not percentage? To calculate the weighted average in such situations, we should use a combination of two functions: SUM.PRODUCT and SUM.

In this example, we also want to calculate the final grade for the student, but the weights are expressed as numbers and their total is 15:

weighted average

In this situation, we should first use SUMPRODUCT to multiply each grade by weight and add the results. The next step is diving received value by the sum of weights calculated by the SUM function:

=SUMPRODUCT(numbers(grades range); weights range)/SUM(weights range)

So, for our example correct formula should be entered as below:


weighted average

We have received information, that final grade for this student should be 4 (3,57 rounded to whole number).

You can calculate weighted average for more than one student, but you have to make sure, that cells reference in formulas will be written correctly with $ sign:

weighted average

Leave a Reply

Your email address will not be published.