How to calculate weighted average in Excel (SUM and SUMPRODUCT formulas)

This tutorial shows you two simple ways to calculate weighted averages in Excel: using the SUM or SUMPRODUCT function.

Previous articles covered basic Excel averaging functions. But what if some values are more important than others, impacting the final average more significantly? That's where weighted averages come in.

While Excel lacks a dedicated weighted average function, other functions effectively handle this calculation, as shown below.

• What is a weighted average?
• Calculating weighted averages in Excel
  • Using the SUM function
  • Using the SUMPRODUCT function

What is a weighted average?

A weighted average is a type of mean where some data points hold more weight than others. Each value is assigned a weight reflecting its importance.

Student grades often use weighted averages, as seen in the example below. A standard average uses the AVERAGE function, but we need a formula that incorporates the weights (column C).

Mathematically, a weighted average is calculated by multiplying each value by its weight, summing the products, and dividing by the sum of all weights.

In this example, the weighted average (overall grade) is:

((910.1)+(650.15)+(800.2)+(730.25)+(68*0.3)) / (0.1+0.15+0.2+0.25+0.3) = 73.5

Note the difference between the standard average (75.4) and the weighted average (73.5).

Calculating weighted averages in Excel

Excel uses the same approach, but simplifies the process using built-in functions.

Using the SUM function

The SUM function provides a straightforward approach:

=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6)/SUM(C2:C6)

This mirrors the manual calculation, using cell references instead of numbers.

The screenshot shows the formula producing the same result as the manual calculation. Observe the difference between the standard average (C8) and the weighted average (C9).

While simple, the SUM formula is inefficient for large datasets. The SUMPRODUCT function is better suited for this.

Using the SUMPRODUCT function

The SUMPRODUCT function is ideal because it sums products directly. Instead of individual multiplications, you provide two arrays (ranges of cells) and divide by the sum of weights:

=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)

With values in B2:B6 and weights in C2:C6, the formula becomes:

=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)

Selecting the array in the formula bar and pressing F9 shows the underlying values:

SUMPRODUCT multiplies corresponding values from the two arrays (e.g., 910.1, 650.15, etc.), sums the products, and returns the total.

Comparing SUMPRODUCT to the SUM formula verifies identical results.

Weights don't need to sum to 100% or be percentages. You can use a priority scale, assigning points to each item, as shown below:

This concludes the tutorial on calculating weighted averages in Excel. Download the sample spreadsheet to practice. The next tutorial will cover moving averages. Practice workbook

Excel Weighted Average - examples (.xlsx file)

The above is the detailed content of How to calculate weighted average in Excel (SUM and SUMPRODUCT formulas). For more information, please follow other related articles on the PHP Chinese website!