Geekers - Previously, we learned how to calculate the average of a group of numbers in Excel using the AVERAGE function.

But, sometimes, when we want to calculate the average over a group of numbers, we also want to define or add certain criteria. Well, to do that, we can use the AVERAGEIF function.

The AVERAGEIF formula or function is used to calculate the average value based on certain single criteria or conditions.

For example, you want to calculate the average score of students' math exam results where only scores that are above or equal to 70 (KKM score) will be counted while those below 70 are ignored.

The AVERAGEIF function syntax is:

AVERAGEIF(range; criteria; [range_average])

The following is an explanation of the arguments in the formula above:

* range :Â Â is a range of data that will be adjusted to certain criteria which can be in the form of cells, ranges, groups of numbers, named ranges, arrays or cell references.

* criteria: are conditions/requirements/criteria that will determine which cells/values we will use as the basis for calculating the average. It can be numbers, logical expressions, text or cell references. If the criteria are in the form of logical expressions and text then they must be enclosed in double quotes (") such as "Social Science" and "<15000".

* range_average : (optional) is the range of data that will be averaged. If this argument is left blank, the range argument will be used.

Example of Using the AVERAGEIF Function

Example 1

Notice the example below:

AVERAGEIF example in Excel 1 AVERAGEIF example in Excel 1

The formulas used in the example above are:

Formula 1:

=AVERAGEIF(C2:C9,"English",D2:D9)

In the formula above, we calculate the average student score in the "Bhs. Indonesia” in excel.

Formula 2:

=AVERAGEIF(D6:D9,">70")

In the above formula, we calculate the average student scores in the D6:D9 range (social science courses) which are only above 70 in excel.

Formula 3:

=AVERAGEIF(B2:B9,"Awe",D2:D9)

In the formula above, we calculate the average value of "Ayu" in social studies and language courses. Indonesian in excel.

Example 2

Consider the second example below:

AVERAGEIF example in Excel 2 AVERAGEIF example in Excel 2

In the example above, we use the wildcard characters in the form of a question mark (?) and a star (*) as the criterion argument. Where, the question mark (?) represents any single character while the asterisk represents many characters.

The two formulas used in the example above are:

Formula 1:

Â =AVERAGEIF(B2:B7,"*Chicken",C2:C7)

In the above formula, we calculate the average price value for food that ends in the word "Ayam" where there are two food names that match these criteria, namely chicken rice and chicken soup and the result is 17500.

Formula 2:

=AVERAGEIF(B2:B7,"?????Chicken",C2:C7)

In the formula above, we calculate the average value of the price of food that ends in the word "Chicken" followed by 5 characters before the word. There are two food names that fit these criteria, namely chicken rice and chicken soup where the result is 17500.

Note: In this function, you can only set one condition or criteria for this function, not more than one. Use AVERAGEIFS to define multiple criteria.

That's all for the discussion this time about how to use the AVERAGEIF function or formula to calculate the average value that meets certain single conditions or criteria. Hopefully useful and good luck. Thank you!

Article Resources

* AVERAGEIF (AVERAGEIF Function) – Office Support | https://bit.ly/2Szv6Q7

* AVERAGEIF Formula – Excel Class | https://bit.ly/2Sz1G4w