How to calculate Weighted Average in Excel (4 easy examples)

A weighted average (also known as the weighted mean) is a calculation that provides an average where values have different levels of importance. In certain scenarios, Excel’s AVERAGE function is not the right chose. It can provide misleading results as it assumes each value has the same impact on the result. Thankfully, we can easily calculate weighted average in Excel.

In this post, we look at the basics of weighted average calculations and look at examples in Excel.

Table of Contents

When to use a weighted average?
Basic calculation
Weighted average in Excel

Weighted average to calculate exam results
Weighted average to calculate average payment terms
Weighted average with conditions
Weighted average cost of capital (WACC)

Common errors
Conclusion

Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

File name: 0206 Weighted Average in Excel.xlsx

Get access

When to use a weighted average?

It’s not always easy to know when to calculate an average vs. a weighted average. Here are some examples to help you see where a weighted average is the correct calculation.

Course results
In education, all assessments and exams do not have the same impact on the final grade. For example, at university, my final paper counted as 40% of my overall result, with other exams all having equal value. Therefore, a weighted average was required to calculate the correct result.
Payment terms
Looking at average payment terms for suppliers can be a way to understand cash flow. For example, if a business has 8 suppliers with varying payment terms from 7 to 60 days, it may be tempting to use an average. However, if one supplier represents 85% of total spend and only that supplier has 7-day terms, then it is necessary to use a weighted average. That one supplier will have a significnat impact on the final result.
Weighted average cost of capital
A company may calculate its weighted average cost of capital, and use this as the discount rate to assess projects/investments. The idea is that if capital costs 4%, then we need projects to make a return larger than 4%. Otherwise, the cost of financing the project is more than the project returns, so it would be better not to invest at all.

The calculations above clearly demonstrate the importance of selecting a suitable calculation method. The average calculations would be mathematically accurate. However, they would be the wrong measure for decision-making.

Basic calculation

Let’s take a look at a basic scenario to understand how to calculate weighted average.

For a qualification, 40% of Year 2 and 60% of Year 3 count towards the final grade. The first year results are not included. One student’s results are as follows.

What is the student’s final grade?

To calculate a weighted average:

Step 1: Each value is multiplied by the corresponding weight
20 x 0% = 0
25 x 40% = 10
85 x 60% = 51
Step 2: Calculate the sum of each item from Step 1
0 + 10 + 51 = 61
Step 3: Divide the result from Step 2 by the SUM of total weights.
61 / (0% + 40% + 60%) = 61%

The final result is 61%

Assuming the pass mark was 60% the student has passed the course.

Weighted average in Excel

We can use simple functions to calculate the weighted average in Excel.

Traditional Excel

=SUMPRODUCT(weights,values)/SUM(weights)

Dynamic Array enabled Excel: Method 1

=SUM(weights*values)/SUM(weights)

To use these formulas, we just need to insert the weights and values based on our data. Let’s look at this with the 3 examples we introduced earlier.

Weighted average to calculate exam results

In this scenario, 10 exams and one written paper contribute to the final result. The written paper is worth 40% of the final result; therefore, each exam is worth 6% of the result.

The formula in cell C17 is:

=SUMPRODUCT(D4:D14,C4:C14)/SUM(D4:D14)

The SUMPRODUCT function achieves Steps 1 and 2 of the calculation; it multiplies each value with its corresponding weight and aggregates the total. Finally, step 3 calculates the result of SUMPRODUCT divided by the sum of the weights.

Exam results are a reasonably straightforward scenario to understand. The total weights add to 100%, so we can have a gut feeling about whether the total looks accurate.

Weighted average to calculate average payment terms

In this scenario, we have one supplier on 7-day terms who is 85% of the cost, and then 7 other suppliers varying from 30 to 60 days.

The values have been entered inside an Excel Table, and we are using a dynamic array-enabled version of Excel. Therefore, we can use a calculation with only SUM functions.

The formula in Cell C14 is:

=SUM(Suppliers[Payments]*Suppliers[Terms])/SUM(Suppliers[Payments])

The first SUM function achieves Steps 1 and 2 (i.e., the multiplication and initial aggregation). Then, in Step 3, the result is divided by the total weights.

The example file include other methods of calculating the result:

Dynamic Array + LET

Using the LET function, we can include variable names. This makes the formula more readable:

=LET(
values,Suppliers[Payments],
weights,Suppliers[Terms],
result,SUM(weights*values)/SUM(values),
result)

Traditional Functions

If you don’t have a dynamic array-enabled version of Excel, then use the SUMPRODUCT method noted above.

=SUMPRODUCT(Suppliers[Payments],Suppliers[Terms])/SUM(Suppliers[Payments])

Weighted average with conditions

Sometimes weighted averages do not tell the whole story. If a company wanted to calculate the average salary of its employees, it may decide to exclude the executives (as they may be paid significantly more than most employees).

In the example below, we calculate the weighted average and only include the standard category.

The formula in cell D13 is:

=SUMPRODUCT((E4:E9)*(D4:D9)*(C4:C9=C13))/SUMIFS(E4:E9,C4:C9,C13)

To calculate correctly, both sides of the divide symbol must include the same items.

SUMPRODUCT((E4:E9)*(D4:D9)*(C4:C9=C13)) – multiples the employee numbers (E4:E9) by the salary (D4:D9) but only where category (C4:C9) equals the word Standard (C13)
SUMIFS(E4:E9,C4:C9,C13) – Sums the employee numbers (E4:E9) where the category (C4:C9) is equal to Standard (C13)

By only including Standard in both functions, it calculates the correct value.

Weighted average cost of capital (WACC)

For this final example, we calculate a common business requirement: weighted average cost of capital using Excel.

We will become a more technical here, to make sure we calculate the correct value.

WACC is based on two elements: Debt (e.g. loans) and Equity (e.g. shareholders).

Interest paid on debt is tax deductible, while dividends paid to shareholders not tax. So, this adds an additional level of complexity.

In practical terms, it means the published interest rate needs to be adjusted for tax before being included in the calculation.

The numbers below are based on an article from investopedia.com. The assumptions are:

Equity (e.g. issued shares) of $394B, which pays dividends at 4.99% of profit.
Debt (e.g. bank loans) of $44B which pays interest at 3.99%.
Tax rate on company profits is 33%.

As interest is tax deductible, we must adjust the interest rate down by 33%, to provide a post-tax rate of 2.67%.

In this example we have used the SUMPRODUCT function.

The formula in cell E8 is:

=SUMPRODUCT(F4:F5,E4:E5)/SUM(F4:F5)

This example demonstrates that not all scenarios are straightforward. We may need to adjust values for other factors prior to performing the calculation.

Common errors

Here are some common errors you might encounter when calculating weighted average in Excel:

In the multiplication, the #VALUE! error is returned if the ranges or arrays are of different sizes. This is true for the SUMPRODUCT or SUM approach.
SUMPRODUCT and SUM both treat text as zeros, so if your ranges contain any text, it may not calculate the correct result