Excel Tip: Calculating Averages on Summary or Grade Data

Many Excel users have used the AVERAGE function to calculate the average of a series of data. But what do you do when you have summary data instead of individual responses and need to calculate an average? (For example, counts of the number of people who selected each rating on a 5-point rating scale like the class evaluation results illustrated in the figure below.)

Figure 1

To arrive at an average in these situations you need to compute a “weighted average”. And while Excel does not have a built-in “weighted average” function, you can easily calculate one with a simple formula which incorporates two Excel functions - the SUMPRODUCT and the SUM function.

How to Calculate a Weighted Average

Using the data in Figure 1, let’s say you want to get the average overall rating for each question. For question 1, nine people strongly agree that they have learned new information in the class. Thirteen people agree, and one said maybe. Using the AVERAGE function would result in an average of 7.7. Of course, this doesn’t make any sense. We should expect an average within the range of the scale (1 to 5).

In order to correctly calculate the average overall response to each question, we need to:

1. Multiply the number of individuals selecting each rating by the corresponding rating value (1 – 5)
2. Add the results of those calculations together
3. Divide that result by the total number of responses to the question

The following simple Excel formula does all that for us for question 1:

=SUMPRODUCT(D4:H4, D3:H3)/SUM(D4:H4)

The SUMPRODUCT formula simplifies our task by multiplying each question by its corresponding value and returning the sum of those products (Steps 1 and 2 above). We then simply divide the sum product by the total number of question responses (step 3).

Using this formula, we find that the average response to question 1 is 4.3.

In an academic environment, this weighted average formula has many applications. For example, calculating the average grade earned by students in a particular course based on summary data…

The formula can be easily adapted to meet other situations, like calculating a student’s grade based on assignments of different weights…

Have More Questions About Excel?

Beyond the Excel help function and a Google search, there are many resources available to assist with Excel.

• Miami’s online self-paced training services (SkillPort and VTC), available via www.muohio.edu/training, have a number of Excel 2007 courses.
• There are several instructor-led Excel 2007 workshops scheduled for April – June 2009, for schedules and to register see Microsoft Office 2007 in TRAIN.
• Excel e-books are available in Safari Books Online (you can search for Excel 2007 or perform a more specific search for a particular Excel topic).
• The IT Services Support Desk at 513-529-7900 or ithelp@muohio.edu can assist with specific questions/problems.