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.