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. Note that this data already contains a number of named ranges. We have “prices,” “beds,” “size,” “listed,” and “status.” 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, and then the criteria. To start off, let’s calculate a straight average with the AVERAGE function for reference. Like AVERAGE and AVERAGEIF, the AVERAGEIFS function will automatically exclude blank values, but zero values are included. Let’s calculate an average that excludes zero values. Although AVERAGEIFS allows you to provide multiple criteria, you can easily use it to get an average based on one condition only. In this case, Average_range is “prices,” criteria_range1 is “prices,” and criteria1 is “greater than zero.” Next, we need to calculate an average for prices greater than zero and less than $500,000. 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 $500,000, we’ll need to supply another range and criteria pair: criteria_range2 is “prices,” and criteria2 is “<500,000”. Using the status bar, I can quickly verify that we do, indeed, have the right average. The AVERAGEIFS function will throw an error if no values in the range meet the criteria. We’ll get a “divide by zero” error if we try to get an average for properties below $100,000 since there aren’t any in the list. To get an average for properties with two or more bedrooms and greater than 2500 SF, average_range is “prices,” criteria_range1 is “beds,” criteria1 is “>=2”; then criteria_range2 is “size,” and 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 again with a criteria of “< Jan 1, 2013.”
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
