Special

Excel Functions

2003-2010

Special

Excel Functions

2007-2010

Lesson 21: AVERAGEIF Function (Excel 2007)

The general format for the AVERAGEIF formula is as follow: =AVERAGEIF(range where criteria applies, criteria, average of what).

In this first example the criteria applies to the range that is averaged:
=AVERAGEIF(D2:D7,">5",D2:D7)
In plain English it says calculate the average of cells D2 to D7 if they are greater than 5. The result should be 6.666.

The criteria can also be in another column. Applied to the table above, the result of the following formula should be 4.666:
=AVERAGEIF(C2:C7,"John" ,D2:D7)
or
=AVERAGEIF(C2:C7,C2 ,D2:D7)
In plain English it says: calculate the average of cells D2 to D7 if the value in cells C2 to C7 is John or the equivalent to the value in cell C2.

WARNING: You can only use AVERAGEIF if you are using Excel 2007 and if everyone who will receive your workbook is also using Excel 2007. You can do the same thing and even more in a version of Excel that is earlier than 2007. You will use SUMPRODUCT the most powerful function in Excel.

Excel Functions and Formulas Sitemap

Go to the next lesson:
Lesson 22: AVERAGEIFS (Excel 2007)