How to Calculate Weighted Averages in Excel With SUMPRODUCT

01
of 01

Excel SUMPRODUCT Function

Finding the Weighted Average with SUMPRODUCT in Excel
Finding the Weighted Average with SUMPRODUCT. © Ted French

Weighted vs. Unweighted Average Overview

Usually, when calculating the 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.

An example would be (2+3+4+5+6) / 5 which gives an unweighted average of 4.

In Excel, such calculations are easily carried out using the AVERAGE function.

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 midterm and final exams would be given a greater weight.

In Excel, weighted averages can be calculated using the SUMPRODUCT function.

How the SUMPRODUCT Function Works

What SUMPRODUCT does is multiply the elements of two or more arrays and then add or sum the products.

For example, in a situation where 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.

Excel SUMPRODUCT Function 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: (required) the first array argument.

array2, array3, ...array255: (optional) additional 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 numbers are entered without being separated by operators, Excel treats them as text data. This situation is covered in the example below.

Note:

  • All array arguments must be the same size. Or, in other words, there must be 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.

Example: Calculate Weighted Average in Excel

The example shown in the image above 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;
  • divided the above sum by the total of the weighting factor 7 (1+1+2+3) for the four assessments.

Entering the Weighting Formula

Like most other functions in Excel, SUMPRODUCT is normally entered into a worksheet using the function's dialog box. However, since the weighting formula 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 following steps were used to enter the weighting formula into cell C7:

  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 following formula into the cell:

    =SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3)

  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

Since the student had better results for his midterm and final exams, weighting the average helped to improve his overall mark.

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 - 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 - since it uses cell references rather than numbers in totaling the divisor - would be to use the SUM function to total the divisor with the formula being:

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

It is usually best to enter cell references rather than actual numbers into formulas as it 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, the first two forms of the formula would have 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 will recalculate the result.