Excel : How to calculate a weighted average efficiently?

Excel : How to calculate a weighted average – A complete and practical guide

In Excel, the weighted average is an excellent tool for giving different importance to each value in an average calculation. Unlike the classic arithmetic average, where each element is treated equally, the weighted average takes into account the relative value of each piece of data.

To apply it in Excel, use the formula :
=SOMMEPROD(values; weight) / SUM(weight)

This is particularly useful in situations where some inputs weigh more than others. For example, in evaluations with varying coefficients, or in sales reports where each product does not have the same quantity sold.

What is the weighted average?

Definition

The weighted average is an average in which each value is assigned a weight, indicating its importance. Each value is multiplied by its weight, added together, then divided by the sum of the weights.

Formula:
Weighted average = (value₁ × weight₁ + value₂ × weight₂ + …) / (weight₁ + weight₂ + …)

Thus, values with a higher weight will have a stronger impact on the result.

Weighted average vs. classic average

The classic average adds up all the values and divides them by their number, without distinction.

Example:

Simple average of 4, 6, 8: (4 + 6 + 8) / 3 = 6
Weighted average with weights 1, 2, 1 :
(4×1 + 6×2 + 8×1) / (1 + 2 + 1) = (4 + 12 + 8) / 4 = 6

Here the result is identical, but this is not always the case. Weighting can be used to accurately represent differences in priority or frequency.

Why use a weighted average in Excel?

This method is very useful in areas such as :

Monitoring individual performance
Analyzing investment portfolios
Evaluating deliverables in project management
Inventory optimization

It enables you to make more realistic and better-informed decisions by taking into account what really counts in the data.

Using Excel for weighted averaging

What you need to know

Excel works on identified cells (e.g. A1, B2…), which are organized into rows and columns. A range of cells (A1:A10) can be used in formulas.

Excel offers numerous functions, including SUM, AVERAGE, SUMPROD and SI, which make it easy to automate complex calculations such as weighted averages.

The key formula to remember

=SOMMEPROD(range_values; range_weight) / SUM(range_weight)

SUMPROD multiplies values and weights line by line, then sums.
SUM calculates the total weight.

Example of calculation with Excel

Let’s take the results obtained in three different areas of a project with varying weightings:

Area	Score	Weighting
Quality	80	0.4
Lead time	70	0.3
Communication	90	0.3

Excel formula :
=SUMPROD(B2:B4; C2:C4) / SUM(C2:C4)

Calculation :
(80×0.4) + (70×0.3) + (90×0.3) = 32 + 21 + 27 = 80

Final result: 80
This result reflects an average adjusted to the priority given to each criterion.

Tips for a reliable calculation

Use named ranges for greater clarity: e.g. “Scores”, “Weights”.
Check that the sum of weights is not equal to 0
Checks cell references when adding/deleting rows
Uses ARRONDI(formula; 1) to limit decimals if necessary
Does not use AVERAGE in this type of calculation, unless all weights are equal

Other useful functions

AVERAGE

=MOYENNE(A1:A5)

Used for a simple average, without weighting.

SUM

=SUM(B1:B10)

Adds all values in a range.

IF

=SI(C1>50; "Valid"; "Invalid")

Allows you to test a condition and adapt the results.

Application examples

Employee evaluation

To evaluate an employee on several axes with different levels of importance:

Criteria	Score	Coefficient
Productivity	75	0.5
Autonomy	90	0.3
Team spirit	85	0.2

Weighted average :
=SUMPROD(B2:B4; C2:C4) / SUM(C2:C4)
Result: 81.5

Investment portfolio

To estimate total return :

Assets	Return	Breakdown
Bonds	5%	50%
Equities	9%	30%
Real estate	7%	20%

Weighted average: 6.9

Inventory management

A company buys two lots:

200 units at €8
100 units at €12

Average value :
= ((200×8) + (100×12)) / (200+100) = 9.33 €

Frequent problems and solutions

Problem	Solution
Wrong range selected	Always check ranges used in formulas
Empty cells	Use `SI` to avoid calculation errors
Inaccurate result	Use `ARRONDI` to normalize decimals
Poorly organized data	Structure columns before writing formulas