1. Computing

Calculate Weighted Average in Excel

Use the SUMPRODUCT Function in Excel to Find the Weighted Average

By

Calculate Weighted Average in Excel

Calculate Weighted Average in Excel

© 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.

An example of calculating a weighted average is shown below.

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

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

Excel SUMPRODUCT Function Syntax 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. The array elements are multiplied and then added together by the function.

Example: Calculate Weighted Average in Excel

The following example will calculate the weighted average for a student's final mark.

The SUMPRODUCT function will multiply the various marks by their weight factor and then add these values together.

This result will then be divided by the total of the weighting factor 7 (1+1+2+3) for the four assessments.

Entering the Data

Enter the data in the table below into cells D1 to F6 of an Excel worksheet.

 
       Student Grading		
  Assessment  Weight   Mark
  Assignments	   1         70
  Tests	           1	      72
  Mid Term           2	      84
  Final Exam	   3	      80
                  		

Entering the Weighting Formula

Because the SUMPRODUCT result will be divided by the weight factor to get the weighted average, the weighting formula will be typed into cell F7.

  1. In cell D7 type the heading Final Mark:

  2. Click on cell F7 - the location where the student's final mark will be displayed

  3. Type the following formula into cell F7 of the worksheet:

    = SUMPRODUCT ( E3:E6, F3:F6) / (1+1+2+3)

  4. Press the Enter key on the keyboard

  5. The answer 78.6 should appear in cell F7 - 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.

©2014 About.com. All rights reserved.