**How To Calculate**

The **Weighted Average** in Excel

**How To Calculate**

The

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 —

SUMandSUMPRODUCT.

Let’s get started! 😊

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

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.

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.

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

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

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

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:

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.

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.

**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:

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! 😊