How to Calculate Weighted Averages in Excel With SUMPRODUCT

What to Know

  • Syntax: =SUMPRODUCT(Array1,Array2,Array3,...Array255).
  • In a weighted average calculation, some numbers in the range are worth more than others.
  • The SUMPRODUCT weighting formula is a non-standard formula that must be typed in a cell to work.

This article explains how to use the SUMPRODUCT function to calculate a weighted average in Microsoft Excel formulas.

Enter the Weighting Formula

Like most other functions in Excel, SUMPRODUCT can be entered into a worksheet using the Functions Library in the Formulas tab. Because the weighting formula in this example uses SUMPRODUCT in a non-standard way (the function's result is divided by the weight factor), the weighting formula must be typed into a worksheet cell.

The 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.
Excel showing the SUMPRODUCT formula in use

To enter the SUMPRODUCT formula to compute a weighted average, open a blank worksheet, enter the data in rows 1 through 6 from the image above, and follow these steps:

  1. Select cell C7 to make it the active cell (this is the location where the student's final mark will display).

  2. Type the formula =SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3) into the cell. The formula appears in the Formula Bar.

  3. Press the Enter key on the keyboard.

  4. The answer 78.6 appears 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 the overall grade.

How the SUMPRODUCT Function Works

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.

SUMPRODUCT multiplies the elements of two or more arrays and then adds the products to calculate weighted averages. 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.

Excel showing a weighing grades example

In a situation in which two arrays with four elements each are entered as arguments for the SUMPRODUCT function:

  • The first element of array1 is multiplied by the first element in array2.
  • The second element of array1 is multiplied by the second element of array2.
  • The third element of array1 is multiplied by the third element of array2.
  • 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.

SUMPRODUCT Syntax and Arguments

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 the plus (+) or minus (-) signs. If you enter numbers that are not separated by operators, Excel treats them as text data.

Array arguments must have the same number of elements 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.

SUMPRODUCT Formula Variations

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) is 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
Excel showing alternative ways to use SUMPRODUCT

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 is changed to 0.5 in the example and Tests is changed to 1.5, the first two forms of the formula need to be edited manually to correct the divisor.

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

Was this page helpful?