AVERAGEIF
AVERAGEIF “returns the average (arithmetic mean) of all the cells in a range that meet a given criteria” - Microsoft Office Support 2019. Notice that it says a given criteria. In other words, one criteria.
Excel Formula: =AVERAGEIF(range,criteria,[average_range])
Note: any component of the formula that is in square brackets means it’s optional.
Formula Made Easy
Example
In my "Cars" data, I want to know the AVERAGE AGE of MERCEDES-BENZ cars (criteria).
Based on the above photo, if I were to fill in cell references into my Formula Made Easy table, it will look like:
Formula Applied
Putting the above references into the Excel formula, it will be:
=AVERAGEIF(A:A,”Mercedes-Benz",E:E)
If my range and average_range were in the same column, I would omit the “average_range” part of the formula.
What if you want to find the average of cells that meet more than one criteria? Then you'd use AVERAGEIFS, which I'll cover in the next post.
Commentaires