How to Calculate an Average in Excel With or Without Weights?
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
| Row | Column A: grade /20 | Column B: weight |
|---|---|---|
| 2 | 12 | 1 |
| 3 | 15 | 2 |
| 4 | 14 | 3 |
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
This content follows Outilo's editorial guidelines.