- Get link
- Other Apps
- Get link
- Other Apps
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.
Comments
Post a Comment