How to Calculate an Average in Excel With or Without Weights?

Created by Outilo Last updated on 19/11/2026
Quick answer

In Excel, use =AVERAGE(A2:A4) for an ordinary average. If grades are in A2:A4 and weights are in B2:B4, use =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4).

Explanation

1. A simple average with AVERAGE

Enter the grades, already on the same scale, in A2, A3 and A4. In another cell, type:

=AVERAGE(A2:A4)

Excel averages the numerical values. Blank cells are not treated the same as an entered zero.

2. Weighted averages with SUMPRODUCT

Put assessment weights into B2, B3 and B4, then type:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

SUMPRODUCT calculates the sum of grade × weight. SUM totals the weights. Both ranges must have the same number of rows, and total weight must be positive.

3. Grades with different maximum scores

Convert grades to a scale of 20 before averaging. If A2 holds the points earned and B2 holds the maximum possible points, enter this in C2:

=A2*20/B2

Copy it down. Put weights in D2:D4 and use:

=SUMPRODUCT(C2:C4,D2:D4)/SUM(D2:D4)

In French Excel, the functions are MOYENNE, SOMMEPROD and SOMME, typically using semicolons between arguments.

Source: Microsoft, calculate an average in Excel.

If you prefer not to build a spreadsheet, use Outilo's free grade-average calculator.

Formula / method

Unweighted: =AVERAGE(A2:A4)

Weighted: =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

Convert to /20: =A2*20/B2 (A2 = grade, B2 = maximum)

Concrete example

Excel sample data

RowColumn A: grade /20Column B: weight
2121
3152
4143

The simple average is 13.67/20.

With =SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4), the result is (12 + 30 + 42) ÷ 6 = 14/20.

Common mistake

SUMPRODUCT does not automatically convert grades from /10 or /15 to /20. Convert them before weighting, and check that total weight is greater than zero.

Calculate my average

Open the tool

Similar questions

Find the right tool.

My favorite tools

Your saved tools, always within reach.