In this video, we'll look at how to use the AVERAGEIFS function to calculate an average from numbers that meet multiple criteria.
Here we have a list of 16 properties with prices and other information. Let's calculate some averages based on the criteria shown in column K.
The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. The first argument is average_range - this is the range that contains the values to average. The criteria is supplied in pairs - first the range, then the criteria.
To start off, let's calculate a straight average with the AVERAGE function for reference. Note that this data, already has a number of named ranges: "prices", "beds", "size", "listed", and "status".
These aren't necessary, but they make the formulas easier to read and copy.
Now let's calculate and average that excludes zero values. AVERAGEIFS will automatically exclude blank values, but zero values are includes because they are numeric.
Although AVERAGEIFS allows you to provide multiple criteria, you can easily use it to get an average based on one condition only. Average_range is "prices", criteria_range is "prices", and the criteria is greater than zero.
Next, we need to calculate an average for prices greater than zero and less than $500k. In this case, Average_range is again "prices", criteria_range1 is "prices", and criteria1 is greater than zero. To limit the average to prices less than $500k, we need to supply another range and criteria pair. criteria_range2 is "prices", and criteria2 is "<500k"
Using the status bar, I can quickly verify that we do indeed have the right average.
Like AVERAGE and AVERAGEIF, AVERAGEIFS will throw an error if no values in the range meet the criteria, so we'll get a divide by zero error if we try to get an average for properties below $100k, since there aren't any in the list.
To get an average for properties with 2 or more bedrooms and greater than 2500 sq ft, Average_range is "prices", criteria_range1 is "beds", criteria1 is ">=2", criteria_range2 is "size", criteria2 is ">2500".
To get an average for properties listed in 2012, we use "prices" for average_range, then we need to supply "listed" twice as a criteria range. Once with a criteria of > Dec 31, 2011 and once with a criteria of > Jan 1, 2013.
Finally, remember that it is often convenient to move assumptions out onto the worksheet where they can easily be referenced and changed.
Here we have bedroom count in column K and we can easily build an AVERAGEIFS formula that uses these values as criteria1 to calculate a different average for properties with 1-5 bedrooms.