Weighted average in Excel: Calculate it in 3 simple steps

Key information

A WEIGHTED AVERAGE corresponds to a type of average where it is possible that the numbers used have a different influence on the result, thus assigning more importance to some than to others. Although Excel does not have a function that allows us to calculate it directly, we can calculate it using the SUMPRODUCT and SUM functions.

The basics

Purpose: He weighted average It is a type of average where the different observations or numbers are accompanied by a number that determines their influence on the result (“weights”).
Characteristics: To generate a weighted average we use Excel functions SUMPRODUCT and ADDITION. The SUMPRODUCT function allows us to select two columns of data, and then provide the sum of the products. In SUMAPRODUCTO it is necessary that the matrices have the same dimensions.
Syntax:

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

=SUM(number1;[number2];…)

Arguments:
- SUMPRODUCT:

matrix1 = First selection of numbers that I want to multiply and then add. If only one matrix is included, the function will only add the numbers.

[matrix2], [matrix3]… = Optional. Other selections of numbers that I want to multiply and then add. You can have up to 255 extra arrays

SUMPRODUCT:

number1 = First of the numbers to add.

number2 = Optional. Other numbers that you want to add.

How does a weighted average work?

As we already mentioned, a weighted average (also known as a weighted average) is simply an average where the data we use may have different relative importance. This means that certain numbers may have more influence on the final result than others. To understand what we mean, let's look at the following example:

As we can see in the example, in this case the weighted average has the same result as the simple average, since the relative importance of each observation (or “weight”) is the same (25%). However, if now one of the observations has a greater influence:

In this case the exam has twice as much importance in the result as the other tests. As the exam is the highest grade and its influence increases with respect to the previous example, the weighted average goes up. On the other hand, the simple average remains the same, since it does not depend on the weights, but rather they are all equal by definition.

Step 1: Using SUMPRODUCT in the weighted average

The first step to calculate the weighted average in Excel is to select the data we want to use and enter it in the SUMPRODUCT function. As we already mentioned, what we need is a selection of data containing the numbers we want to average along with a column indicating their “weights”. In the following example we have the evaluation of the attributes of a product, along with its influence on the final evaluation:

As we see, product attributes have different influences on the result. For example, the “Price” attribute has three times the effect of the “Brand” attribute in the weighted average formula.

We can see that the arguments of the SUMPRODUCT function are:

matrix1 = C3:C6 = Numbers to average (“Note” Column)
matrix2 = D3:D6 = Influence of each number on the result (“Weight” Column)

With this function Excel will multiply each number by its corresponding weight, and then add the results. So in the weighted average example you will first multiply 75 by 2, then 40 by 3, and so on. Adding the results we obtain the following:

Step 2: Using SUM in the weighted average

The second step in the formula to obtain weighted average in Excel consists only of adding the weights using the SUM function. This is done because the weights can be any number, but it is necessary to see their effect with respect to the total. For example, a weight of 5 is not much when the total is 100, but it is when it is 10. Returning to our example:

With which we obtain the following:

Step 3: Calculating the weighted average

Having now the two components of our weighted average formula in Excel, the only thing left to do is divide the result of the SUMPRODUCT by the SUM of the weights:

Thus arriving at the weighted average:

As we can see the weighted average of the attributes is 56.

It is not necessary for each of the functions to be in a different box, we can include everything in the same one:

Weights as part of a total

One way to avoid having to include the SUM function when calculating the weighted average in Excel is to represent each weight as part of the total, that is, dividing each by the total sum of weights. Returning to the previous example, we divide each peso by the total number of pesos (8):