How to Calculate Weighted Averages in Excel With SUMPRODUCT

Hand Touching Abacus
Don't go old-school with your data, use Excel to calculate weighted averages.

 Getty Images / Yunio Baro Gomez / EyeEm

Typically, when you calculate an average or arithmetic mean, each number has equal value or weight. The average is calculated by adding a range of numbers together and then dividing this total by the number of values in the range. A weighted average, on the other hand, considers one or more numbers in the range to be worth more or have a greater weight than the other numbers.

For example, certain marks in school, such as midterm and final exams, are usually worth more than regular tests or assignments. If averaging is used to calculate a student's final mark, the midterms and final exams would be given greater weights. In Excel, you can calculate weighted averages using the SUMPRODUCT function.

01
of 04

How the SUMPRODUCT Function Works

Screenshot of Excel showing the SUMPRODUCT Function.

SUMPRODUCT multiplies the elements of two or more arrays and then adds the products. For example, in a situation in which two arrays with four elements each are entered as arguments for the SUMPRODUCT function:

  1. The first element of array1 is multiplied by the first element in array2.
  2. The second element of array1 is multiplied by the second element of array2.
  3. The third element of array1 is multiplied by the third element of array2.
  4. The fourth element of array1 is multiplied by the fourth element of array2.

Next, the products of the four multiplication operations are summed and returned by the function as the result.

02
of 04

SUMPRODUCT Syntax and Arguments

Screenshot of Excel showing a weighing grades example.

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments. The syntax for the SUMPRODUCT function is:

=SUMPRODUCT(array1, array2, array3, ...array255)

The arguments for the SUMPRODUCT function are:

  • Array1: The first array argument (required)
  • Array2, array3, ...array255: Additional (optional) arrays, up to 255. With two or more arrays, the function multiplies the elements of each array together and then adds the results.

The array elements can be cell references to the location of the data in the worksheet or numbers separated by arithmetic operators, such as plus (+) or minus (-) signs. If you enter numbers that are not separated by operators, Excel treats them as text data.

  • All array arguments must be the same size. In other words, the same number of elements must be in each array. If not, SUMPRODUCT returns the #VALUE! error value.
  • If any array elements are not numbers, such as text data, SUMPRODUCT treats them as zeros.
03
of 04

Entering the Weighting Formula

Screenshot of Excel showing the SUMPRODUCT Formula in use.

Our example shown in this article calculates the weighted average for a student's final mark using the SUMPRODUCT function; the function accomplishes this by:

  • Multiplying the various marks by their individual weight factor.
  • Adding the products of these multiplication operations together.
  • Dividing the above sum by the total of the weighting factor 7 (1+1+2+3) for the four assessments.

Like most other functions in Excel, you'd normally enter SUMPRODUCT into a worksheet using the Formula Builder; however, because the weighting formula uses SUMPRODUCT in a non-standard way, however — the function's result is divided by the weight factor — you must type the weighting formula into a worksheet cell.

=SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3)
  1. Click on cell C7 to make it the active cell (the location where the student's final mark will be displayed).
  2. Type the above formula into the cell.
  3. Press the Enter key on the keyboard.
  4. The answer 78.6 should appear in cell C7 (your answer may have more decimal places).

The unweighted average for the same four marks would be 76.5. Because the student had better results for his midterm and final exams, weighing the average helped improve his overall mark.

04
of 04

SUMPRODUCT Formula Variations

Screenshot of Excel showing alternative ways to use SUMPRODUCT.

To emphasize that the results of the SUMPRODUCT function are divided by the sum of the weights for each assessment group, the divisor — the part doing the dividing — was entered as

(1+1+2+3
The overall weighting formula could be simplified by entering the number 7 (the sum of the weights) as the divisor. The formula would then be:
=SUMPRODUCT(B3:B6,C3:C6)/7

This choice is fine if the number of elements in the weighting array is small and they can easily be added together, but it becomes less effective as the number of elements in the weighting array increases, making their addition more difficult.

Another option, and probably the best choice, given that it uses cell references rather than numbers in totaling the divisor, would be to use the SUM function to total the divisor. The formula is then:

=SUMPRODUCT(B3:B6,C3:C6)/SUM(B3:B6)

It is usually best to enter cell references rather than actual numbers into formulas. This simplifies updating them if the formula's data changes.

For example, if the weighting factors for Assignments was changed to 0.5 in the example and for Tests to 1.5, you'd have to edit the first two forms of the formula manually to correct the divisor.

In the third variation, only the data in cells B3 and B4 need to be updated, and the formula will recalculate the result.