Enter the Weighting Formula
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.
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: 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. 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: 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.
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: 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: 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: 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.
