Pinterest Pixel

Excel’s AVERAGEIF Function – With Examples

The AVERAGEIF function is one of the most used statistical functions in Excel. It allows you to... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Excel’s AVERAGEIF Function - With Examples | MyExcelOnline Excel’s AVERAGEIF Function - With Examples | MyExcelOnline

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:

AVERAGEIF Function

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 –

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

Syntax

The syntax for AVERAGEIF is straightforward:

AVERAGEIF Function

=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.
See also  Having Trouble Understanding Complex Formulas in Excel?

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.

AVERAGEIF Function

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.”

AVERAGEIF Function

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.

AVERAGEIF Function

AVERAGEIF Function

As you can see, the formula provided us the total average units sold for Toyota car models.

See also  How to Flip Data in Columns and Rows in Excel

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)

AVERAGEIF Function

AVERAGEIF Function

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.

AVERAGEIF Function

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.

See also  How to Calculate Percentage Difference with Excel Formula

AVERAGEIF Function

AVERAGEIF Function

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.

AVERAGEIF Function

Wildcards

Suppose this is what your data looks like:

AVERAGEIF Function

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”.
AVERAGEIF Function

AVERAGEIF Function

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:

See also  How to Change Footer in PowerPoint Fast

Enter the formula =AVERAGEIFS(D2:D21,B2:B21,F3,C2:C21,”Silverado”).

AVERAGEIF Function

AVERAGEIF Function

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.

If you like this Excel tip, please share it
Excel’s AVERAGEIF Function - With Examples | MyExcelOnline Excel’s AVERAGEIF Function - With Examples | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...