Excel is a powerful tool to quickly and easily calculate averages - both the regular (arithmetic) average and the weighted average. In this guide, you'll learn how to do this.
Arithmetic average in Excel
The arithmetic average is the sum of all numbers divided by the count of numbers.
Excel formula: =AVERAGE(range)
Example step by step:
- Type your grades in cells A1:A5 (e.g., 7, 6, 8, 7, 5)
- Click in an empty cell (e.g., A6)
- Type the formula: =AVERAGE(A1:A5)
- Press Enter
Result: 6.6
Weighted average in Excel
The weighted average takes into account weights - exams typically weigh more than quizzes.
Excel formula: =SUMPRODUCT(grades,weights)/SUM(weights)
Example step by step:
- Type grades in column A (A1:A3): 7, 6, 8
- Type weights in column B (B1:B3): 3, 1, 2
- Type in cell C1 the formula: =SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3)
- Press Enter
Result: 7.17
Calculation: (7×3 + 6×1 + 8×2) / (3+1+2) = (21+6+16)/6 = 43/6 = 7.17
Tips and tricks
- You can easily add more grades by adjusting the range in the formula
- Excel automatically recalculates the average when you change values
- Use number formatting (1 decimal) for better readability
- Create separate worksheets for different subjects
- Add a column with dates to track your progress
What about Google Sheets?
All the above formulas work exactly the same in Google Sheets (Google Spreadsheets). You can copy the data and formulas directly - they work without modifications.
Prefer an online calculator?
If you don't want to work with Excel, use our free online calculator. It works directly in your browser and you don't need to install anything.