How To Calculate
The 
Weighted Average in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Calculating averages is one of the most common tasks people do on a regular basis.

But when the values have varying importance (or weight) against each other, you should use the weighted average instead.

In this tutorial, we’ll look at the weighted average and how to calculate one using two closely related functions — SUM and SUMPRODUCT.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started! 😊

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

The Weighted Average

By definition, an average is a single, central calculated value of a set of numbers.

A weighted average is a type of average where you take into account — by multiplication — the predetermined weight or importance of each value in a set.

Kasper Langmann, Co-founder of Spreadsheeto

To illustrate, here are their formulas:

v = value

w = weight

average = (v1 + v2+ v3)/total number of v

weighted average = [(v1 x w1)+(v2 x w2)+(v3 x w3)…]/w1+w2+w3

It’s clear that the computation of a simple average and a weighted average produces different results.

You only calculate the average when the values in the data set count equally. If some of the values carry more weight in the computation, the weighted average should be used.

Kasper Langmann, Co-founder of Spreadsheeto

In Excel, there are two functions you can use to compute the weighted average:

  • SUM
  • SUMPRODUCT

We’ll talk more about them later.

Applications of Weighted Average

This may be your first time hearing about the weighted average. But you have already been on the opposite side of this formula.

Remember school?

Your teachers used a weighted average to compute your final grades.

Grade computation is the most common application of weighted average.

Kasper Langmann, Co-founder of Spreadsheeto

Graded school activities — quizzes, exams, presentation, homework, and others have different weights on your final grade. That’s why using a simple average won’t do.

Aside from calculating grades, a weighted average is also used in:

  • Price-weighted index (like the Dow Jones Industrial Average)
  • Product costing
  • Portfolio yields (in financial investments)
  • Average speed
  • Inventory accounting and valuation
  • And many others

Get your FREE exercise file

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

Download the FREE Exercise File

Download exercise file
Download free exercise file

The ‘Normal’ Average

To illustrate the difference in using a normal average and a weighted average, let’s compute for the normal average first.

Let’s use the most common scenario for this tutorial — grading calculations.

Kasper Langmann, Co-founder of Spreadsheeto

As shown below, graded activities are listed along with their corresponding points and weight:

overview of data for weighted average exercise

The equation for manually computing the average would be as follows:

average = 89+92+95+90+92+91/6

The easiest way to do this is by using the ‘AVERAGE’ function with the syntax:

=AVERAGE(number 1, [number 2], …)

It’s a straightforward computation of the central value of the set.

To make matters simpler, let’s feed the range on the formula instead of referencing each of the cells:

average formula

The average is 91.5.

Using the SUM Function

For the weighted average, here’s the complete manual equation:

wa = (89*10%)+(92*20%)+(95*5%)+(90*15%)+(92*20%)+(91*30%)/10%+20%+5%+15%+20%+30%

Pretty daunting, right?

As mentioned earlier, when computing for the weighted average, you’ll have to multiply the values to their weights and add the products

Then, instead of dividing the result with the total number of values, you’ll have to divide the product of the first half of the formula with the sum of all the weights.

Now, let’s simplify the weighted average computation by using the ‘SUM’ function in Excel.

Here’s the syntax of ‘SUM’:

=SUM(number 1, [number 2], …)

When applied to the weighted average formula, we’ll then arrive at:

wa =SUM(B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)/SUM(C3:C8)

In the formula above, the SUM function shortened the 2nd half of the formula where the weights are added.

formula and result of sum function on weighted average

The weighted average is 91.25. That’s a 0.25 difference compared to its normal average.

But you know, the first part of the equation is pretty exhausting. What if you have, let’s say, 100 values?

If you manually reference each cell, you’re bound to get mistakes sooner or later. The best solution for that is using the SUMPRODUCT function.

Kasper Langmann, Co-founder of Spreadsheeto

Using the SUMPRODUCT Function

Remember the part where you have to add the products of each point-weight pair on the data set?

The ‘SUMPRODUCT’ does it for you.

Here’s the syntax of ‘SUMPRODUCT’:

=SUMPRODUCT(array1, [array2], [array3], …)

Instead of manually multiplying a point with its weight then adding them up, all you need to do is supply the ‘array’ which contains the data.

An ‘array’ in this context is a continuous range of cells

In our example, the “Points” array or range is B3:B8. The “Weight” array is C3:C8.

When applied to the function’s syntax, the first part of the formula becomes:

=SUMPRODUCT(B3:B8,C3:C8)

To complete the weight average computation, you’ll have to combine the first and the last half of the weighted average formula:

wa =SUMPRODUCT(B3:B8,C3:C8)/SUM(C3:C8)

Here’s how it looks in Excel:

formula and result of weighted average

As you can see, using the ‘SUMPRODUCT’ function to calculate the weighted average is easier than the ‘SUM’ function. It’s also ideal for computing weighted average on a large data set.

Conclusion

Computing the weighted average might seem like a hassle when done in paper. However, Excel has useful functions like SUM and SUMPRODUCT to simplify the computation.

Don’t worry if you won’t be able to take it all right away. The key here is practice and understanding.

Once you understand the basic formula of finding the weighted average and the Excel functions associated, everything becomes easier and faster! 😊

Kasper Langmann, Co-founder of Spreadsheeto
2019-09-04T13:06:15+00:00