How to Calculate Average in Excel (With Formula Examples)
What is average? It is simply the arithmetic mean of a given set of numbers.
Averages are super easy to calculate – no doubt about that 🎉
But if your dataset gets long, it might be that easy to calculate averages manually. And who would want to spend hours on something worth a few clicks only?
Excel offers a multitude of ways to calculate averages in Excel. And we will look into them all.
So what are we waiting for? Let’s jump right into the guide below.
Also, download our free sample workbook here to tag along with the guide as you continue reading 📩
Table of Contents
First of all other methods, Excel offers an in-built AVERAGE function to calculate averages.
Want to see how the AVERAGE function works in Excel? Let’s go 🚗
The image below shows the marks of a student in different subjects.
The total marks for all the subjects are 100. So how much did Nica score overall (on average)? To calculate that.
- Activate a cell and start writing the AVERAGE function as follows:
= AVERAGE (
When you average cells in Excel, know that the AVERAGE function will ignore logical values and empty cells but include any cells containing zero values 0️⃣
Kasper Langmann, Microsoft Office Specialist- Specify the data range that is to be averaged.
= AVERAGE (B2:B7)
Pro Tip!
You can specify the data range as cell references like = AVERAGE (B2, B3, B4, B5, B6, B7).
Or you can write this argument as a reference to a whole cell range like = AVERAGE (B2:B7).
And if you don’t have the figures populated in cells, you can write the values inside the function yourself as = AVERAGE (10, 12).
- Press Enter, and you’re good to go.
Nica scored 82 marks on average. That’s a good score by the way 🥇
Calculate the average with a formula
We’ve tried using the AVERAGE function of Excel to calculate averages. It’s now time for something more interesting.
Pro Tip!
Do you know what is meant by average? Average is the sum of a given set of numbers divided by the count of those numbers 👩🏫
For example, what is the average of 2,4, and 6?
Count the numbers. There are 3 of them.
Divide the sum by the count (12/3 = 4).
Can we write our formula to calculate the average in Excel? Certainly yes. Let’s again calculate the average marks for Nica with a formula.
- Add up all the marks by writing the following formula.
= (B2 + B3 + B4 + B5 + B6 + B7)
- Divide the formula above by the number of values.
We have 6 subject marks here, so we will divide the formula above by 6 6️⃣
= (B2 + B3 + B4 + B5 + B6 + B7) / 6
- Press Enter to get the average marks as below.
82 – same as above! This tells that the AVERAGE function does nothing different than what we did above 👆
Calculate the average with the SUM and COUNT formula
The ways for calculating averages in Excel have yet not ended. You can calculate averages by combining other functions of Excel.
For example, the functions SUM and COUNT. How? Let’s see here 👀
We are again calculating the average marks for Nica (yes, once again), but this time with a different method. So let’s begin.
- Write the SUM function as follows:
= SUM (B2:B7)
The SUM function will sum up all the marks ➕
- Divide the above formula by the COUNT function as follows:
= SUM (B2:B7) / COUNT (B2:B7)
The COUNT function counts the number of values in the referred cell range.
The COUNT function counts all the cells in the defined cell range. As we have defined the cell range B2:B7, it will count the cells between B2 to B7.
Kasper Langmann, Microsoft Office Specialist- Press Enter to get the following results.
And there it is! Excel computes the AVERAGE of the given numbers.
Easy, no 😍
That’s it – Now what?
And that’s how you can calculate averages in Excel. It is super simple – and now you know that too. There are multiple ways how you can calculate averages in Excel.
Using a formula, the SUM and COUNT formula, and obviously, through the AVERAGE function in Excel.
However, just like calculating averages, there’s so much more that you can do in Excel using other functions. Some of my go-to Excel functions include the VLOOKUP, SUMIF, and IF functions of Excel.
These functions would make your life a lot easier. I suggest you learn them right away ✈
But How? My 30-minute free email course will help you learn them (and many more) Excel functions in no time.
Other resources
There’s not only one function dedicated to calculating averages in Excel – but much more. Like the AVERAGEA function, AVERAGEIF function, and AVERAGEIFS function. Learn them here.
You’d also be surprised to know that Excel doesn’t offer an in-built function to calculate weighted average. But you can still calculate it in Excel using other functions. How? Learn here.