Usually, 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. For example: (2+3+4+5+6)/5 yields an unweighted average of 4. In Excel, you can carry out such calculations easily 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 midterms and final exams would be given greater weights. In Excel, you can calculate weighted averages using the *SUMPRODUCT* function.

###
How the SUMPRODUCT function works

SUMPRODUCT multiplies the elements of two or more arrays and then adds (sums) the products.

For 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's 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 plus (+) or minus signs (-). If you enter numbers that are not 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. 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.

###
Example: Calculate weighted average for a final grade

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
- Dividing 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, you'd normally enter SUMPRODUCT into a worksheet using the function's dialog box. 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.

The following steps were used to enter the weighting formula into cell C7:

- Click on cell
**C7**to make it the active cell (the location where the student's final mark will be displayed). - Type the following formula into the cell:
`=SUMPRODUCT(B3:B6,C3:C6)/(1+1+2+3)`

Press the

**Enter**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.

###
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, 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.