Yahia El-Shall - How to Use the AverageIF Function

By Yahia El-Shall

Here in this procedure we will review how you can use the AVERAGEIF function to calculate an average from numbers that meet a single criteria.

Here we have a list of 5 business for sale with revenue per year and other information. We will calculate some averages based on the conditions listed in column C.

The AVERAGEIF function calculates an average for cells that meet a single criteria. It takes three arguments: range, criteria, and average_range.

The average_range is optional and represents the actual set of cells to average. If you don't use average_range, the range will be used.

Naming your ranges (can be named as column headers in a table) make it easier to use in your formulas. 

Here is our table. 

City	Business	Space (sf)	Revenue, year
San Francisco	Book Store	1666	250,000
Houston	Coffee Shop	1200	300,000
Tampa	Dry Cleaner	1100	500,000
Houston	Ice Cream Store	1200	450,000
Atlanta	Mobile Phone Repair Kiosk	150	125,000

Let's get the average revenue per year for Houston business. To do this we will use the AverageIF formula as follows:

=AVERAGEIF(Table1[City], "Houston", Table1[Revenue, year])

The City column is our range, "Houston" is our criteria, and our average_range is the Revenue Column. 

AVERAGEIF will give  an error if the criteria does not apply to any data in the range. 

When you want to apply the criteria to a different range from the range you're averaging, you'll need to supply the optional argument, average_range

To calculate an average based on more than one criteria, you'll need to use the AVERAGEIFS function. We will cover that in another post.