Calculate Average in Excel: Guide and Formula

Learn how to calculate arithmetic and weighted average of grades in Microsoft Excel with simple formulas

Microsoft Excel is a powerful tool for calculating grade averages. Whether you need a simple arithmetic average or a more complex weighted average, Excel offers simple functions to make your work easier.

Calculate grade average

The AVERAGE Function for Arithmetic Mean

To calculate the simple arithmetic average of grades, use the AVERAGE() function. This function automatically adds all values and divides them by their count.

Formula

=MITTELWERT(A1:A10)

Where A1:A10 is the cell range containing your grades.

Weighted Average in Excel

To calculate the weighted average, where different grades have different weights (e.g., an exam has more weight than a small test), use a combination of SUMPRODUCT and SUM.

Formula for Weighted Average

=SUMMENPRODUKT(A1:A10;B1:B10)/SUMME(B1:B10)

Where A1:A10 are the grades and B1:B10 are their weights.

Calculate grade average

Step-by-Step Guide

How to calculate the average in Excel:

  1. 1 Enter the grades in a column (e.g., A1:A10)
  2. 2 If calculating weighted average, enter the weights in a second column (e.g., B1:B10)
  3. 3 In an empty cell, enter the formula =AVERAGE(A1:A10) or =SUMPRODUCT(A1:A10,B1:B10)/SUM(B1:B10)
  4. 4 Press Enter and Excel will automatically calculate your average

Tips and Tricks

  • The AVERAGE function ignores empty cells, so you don't have to enter all grades at once
  • You can use the AVERAGEIF function to calculate the average of only grades that meet a certain condition
  • To round the result, use the ROUND function, e.g., =ROUND(AVERAGE(A1:A10),2)

Conclusion

Excel is a great tool for calculating grade averages. Whether you need a simple arithmetic average or a more complex weighted average, Excel offers functions that significantly simplify the calculations. With some practice, you'll be able to calculate the grade average in seconds.

Frequently Asked Questions About Excel Averages

What is the average function called in German Excel?
The English function AVERAGE() is called MITTELWERT() in German. If you're using a German Excel version, use that name.
How do I round the average to one decimal place?
Use the ROUND function: =ROUND(AVERAGE(A1:A10),1) rounds to one decimal place, e.g., 4.7.
Can I exclude cells with 0 from the average?
Yes, use AVERAGEIF: =AVERAGEIF(A1:A10,"<>0") calculates the average only of values that are not 0.
Does this also work in Google Sheets?
Yes, the functions AVERAGE, SUMPRODUCT, and SUM work identically in Google Sheets.