Download Excel Workbook
The AVERAGEIF function is one of the most used statistical functions in Excel. It allows you to calculate the average of a range of numbers based on one condition. For example, let’s say we wanted to know the average sales number for Toyota cars. This is what it would look like:
In this article, we will delve into the steps of how to use Excel’s AVERAGEIF function, providing real-world examples to demonstrate its applications –
Table of Contents
Let us look at each of these methods in detail.
Download the Excel Workbook below to follow along and understand how to use Excel’s AVERAGEIF Function –
download excel workbookAVERAGEIF-Function.xlsx
Table of Contents
Syntax
The syntax for AVERAGEIF is straightforward:
=AVERAGEIF(range, criteria, [average_range])
- range: The range of cells that you want to apply the criteria to. In our above example, the range is the Manufacturer.
- criteria: The condition that determines which cells to include in the average. In our example, it is Toyota.
- [average_range]: (Optional) The actual cells containing values or numbers to average. If not specified, the function will automatically average the range values.
How to Use the AVERAGEIF Function
Let’s look at this data regarding the 25 bestselling cars of 2023. We are tasked to find the average unit sold numbers for Toyota Cars only:
On cell E3, enter in =AVERAGEIF(A2:A26. This represents the range of cells that will be tested to determine if they meet the criteria. In other words, the formula will only look for the word “Toyota” in this range.
Continue on with the formula =AVERAGEIF(A2:A26,”Toyota”
The function will test each value in the range A2 to A26 to see if it matches the word “Toyota.”
Finish the formula with =AVERAGEIF(A2:A26,”Toyota”,C2:C26)
This means that for every value in the range A2 to A26 that matches the word “Toyota,” the associated numbers in C2 to C26 will be averaged.
As you can see, the formula provided us the total average units sold for Toyota car models.
Value From Another Cell
An easier way to make your data more dynamic is to use a value from another cell. Let’s take the same data from above. We are still looking for the average units sold numbers for Toyota cars, but this time we will use a cell reference. We will have the word “Toyota” in cell E2.
Enter the formula =AVERAGEIF(A2:A26,E2,C2:C26)
As you can see, the formula returns the average of numbers in C2:C26 that matches the word “Toyota” from cell E2.
The great thing about this is that the formula is dynamic. Meaning if we change the text in cell E2 to Ford, then the formula will calculate the average units sold for Ford models only.
AVERAGING Values Based on Date Range
Let’s say you have a dataset with dates and sales figures. You want to find the average sales for a specific month. The AVERAGEIF function can simplify this task.
Enter the formula =AVERAGEIF($B$2:$B$21,F3,$D$2:$D$21) on cell G3. We included the dollar sign ($) because we want to pin down the ranges, B2:B:21 and D2:D21, as absolute references. This way, the ranges will not move even if we apply the formula to the rest of the columns using Flash Fill.
This formula will calculate the units sold for all entries within the month of January.
Double-click on the square on the bottom right-hand corner of the cell to apply the formula to the rest of the months.
Wildcards
Suppose this is what your data looks like:
You are tasked with finding the average units sold numbers for Honda car models. It would be tedious to create a formula for each Honda car. We want to This is where the wildcards come in handy:
Enter the formula =AVERAGEIF(A2:10,”*Honda*”,B2:B10). The asterisk (*) is the wildcard, matches any sequence of characters that contains “Honda”.
As you can see, the formula calculated the average sales for all the cars containing the word “Honda.”
AVERAGEIFS: AVERAGEIF with Multiple Criteria
What if you needed to average the units sold for a specific model and a particular month? This is where the AVERAGEIFS Function becomes handy:
Enter the formula =AVERAGEIFS(D2:D21,B2:B21,F3,C2:C21,”Silverado”).
This formula calculates the average units sold for the Silverado within the month of January.
Conclusion:
There you have it! The AVERAGEIF function is one of the most useful tools there are in Excel. Pair it with wildcards and the AVERAGEIFS function for the ultimate data analysis ease.
Learn more about Formulas with our 101 Advanced Excel Formulas & Functions Examples.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
