Here we have a list of 16 properties with prices and other information.
It takes three arguments:range, criteria, and average_range.
Note thataverage_rangeis optional and represents the actual range to average.
If you’re not usingaverage_range, therangeargument will be used instead.
First, I’m going to name some ranges to make the formulas easier to read and copy.
Now we can easily refer to “prices,” “size,” and “status.”
To start off, let’s calculate a straight average with theAVERAGE function, just for reference.
Now let’s calculate an average that excludes prices equal to zero.
As it’s possible for you to see, theAVERAGE functionautomatically includes zero values because they are numeric.
To exclude zero values, we need to provide a criteria of greater than zero.
Forrange, we use “prices” and forcriteria, we use “>0” in double quotes.
Now let’s calculate an average for houses with a price less than $500,000.
We need to use “prices” forrangeand
“<500,000” forcriteria.
AVERAGEIF will throw an error if the criteria does not apply to any data in the range.
Theaverage_range is"prices."
To calculate an average for properties with a status of “Sold” the pattern is similar.
Therangeis “status,“criteriais “sold,” andaverage_rangeis “prices.”
