How to Calculate Weighted Average in Excel

The most efficient way to calculate a weighted average in Excel is by combining the SUMPRODUCT and SUM functions.

Step by Step

1. Enter your grades in column A (A2:A6)
2. Enter your weights in column B (B2:B6)
3. In an empty cell, type: =SUMPRODUCT(A2:A6, B2:B6)/SUM(B2:B6)

Example

If A2:A6 contains grades (7, 8, 9, 6, 10) and B2:B6 contains weights (20%, 20%, 30%, 10%, 20%):

=SUMPRODUCT(A2:A6, B2:B6)/SUM(B2:B6) returns 8.1

If you prefer a more transparent approach:

1. In column C, multiply each grade by its weight: =A2*B2 and drag down
2. Sum column C: =SUM(C2:C6)
3. Sum column B: =SUM(B2:B6)
4. Divide: =C7/B7

• Use percentage format for weights (20%, 30%) or enter them as decimals (0.2, 0.3)
• If your weights add up to 100%, you can skip the division — but it is safer to include it always
• Save your spreadsheet as a template for future calculations