When you work with large Excel spreadsheets, you’re bound to need the average of a data set sooner or later. You can add up all of the cells and divide them by the total number, but that’s usually slow and tedious. If you really want to save time, you have to know how to find the mean in Excel.
Just like almost every other Excel feature, you can reach your goal in a couple of different ways. Yes, you can add and divide as mentioned above, but there are multiple formulas to make life so much easier. Here’s what you need to know:
How to find the mean in Excel:
In Excel, there are four different formulas that you can use to find the mean. Those formulas are:
- AVERAGE – returns the average of a set of numbers
- AVERAGEA – returns the average of any data including numbers, booleans, and text
- AVERAGEIF – finds an average of cells based on a single criteria
- AVERAGEIFS – finds an average of cells based on multiple criteria
For this guide, we’ll start with the basics and work through each option.
1. Say you have the spreadsheet shown below. Now you want to get the average of cells B2 through B8.
2. Type =AVERAGE( in the formula bar.
3. Add the cells you want to use. Either type B2:B8 making sure to separate with a colon or click and drag from B2 to B8.
4. Hit Enter and you’ll see your result.
If you want to take the average of a more specific data set — in our example we’ll use apples — you may find it better to use an AVERAGEIF formula.
1. Start the same way as above, enter =AVERAGEIF( into the formula bar.
2. Now you have to enter two sets of information into the formula. First, enter A2:A8 to find just the fruit cells. Then enter A2 to note that you want to search for apples. The final part of your formula should read B2:B8 to find the average of your sales. The formula should read as follows: =AVERAGEIF(A2:A8, A2, B2:B8).
3. Hit enter to see your result!
The AVERAGEIFS formula works the same way as the AVERAGEIF formula, but you can use multiple criteria instead.
Related: How to find duplicates in Excel
What else can I do in Excel?
If you’ve already mastered means in Excel, you might be wondering what’s next. It doesn’t take too much work to beef up your Excel skills, you just need to find the right kind of training. You can get started with a hands-on learning kit from Tech Deals and save big right now.
The deal we’ve found is the Complete Excel Bundle: Startup Toolbox and it includes 14 modules to help you get started. You can work on topics including formulas, VBA, conditional formatting, and many more. If you’ve wondered how to do something in Excel, you’ll probably find the answer you’re looking for in this learning kit.
The 14 total modules have a combined retail value of $2,800 but you can grab them all and dive in for just $39 right now. 450 people have signed up so far and are already practicing their new skills.