Excel AVERAGEIFS Function

AVERAGEIFS Function

The AVERAGEIFS function is a premade function in Excel, which calculates the average of a range based on one or more true or false condition.

It is typed =AVERAGEIFS:

The conditions are referred to as critera1, criteria2, .. and so on, which can check things like:

• If a number is greater than another number >
• If a number is smaller than another number <
• If a number or text is equal to something =

The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the conditions.

The average_range is the range where the function calculates the average.

Example AVERAGEIFS function

Find the average defense of Grass type 1st Generation Pokemon:

The conditions are that the type is "Grass" and Generation is 1.

Name Type 1 Defense Generation Bulbasaur Grass 49 1 Type Gen. Average Defense Ivysaur Grass 63 1 Grass 1 Venusaur Grass 83 1 Fire 1 Charmander Fire 43 1 Grass 2 Charmeleon Fire 58 1 Fire 2 Charizard Fire 78 1 Chikorita Grass 65 2 Bayleef Grass 80 2 Meganium Grass 100 2 Cyndaquil Fire 43 2 Quilava Fire 58 2 Typhlosion Fire 78 2

Copy Values

Example AVERAGEIFS function, step by step:

1. Select the cell H3
2. Type =AVERAGEIFS
3. Double click the AVERAGEIFS command

4. Specify the range for the average C2:C13 (the Defense values)
5. Type ,
6. Specify the range for the first conditionB2:B13 (the Type 1 values)
7. Type ,
8. Specify the criteria (the cell F3, which has the value "Grass")
9. Type ,
10. Specify the range for the second conditionD2:D13 (the Generation values)
11. Type ,
12. Specify the criteria (the cell G3, which has the value "1")
13. Hit enter

The function now calculates the average defense value of the 1st Generation Grass type Pokemon: Bulbasaur, Ivysaur and Venusaur.

The function can be repeated for Fire type Pokemon and 2nd Generation to compare them:

Now, we can see the average defense values of each type between generations:

Notice how the 2nd Generation Grass type Pokemon got more defense, but the Fire type stayed the same.