In this article, we will learn how to use the AVERAGEIF and AVERAGEIFS functions in Excel to calculate the average of a range of cells that meet one or more criteria. These functions are useful when you want to analyze data based on certain conditions, such as finding the average sales of a specific product, the average score of students who passed an exam, or the average temperature of a certain month.
The AVERAGEIF function returns the average (arithmetic mean) of all the cells in a range that meet a given criterion. The syntax of the AVERAGEIF function is:
=AVERAGEIF(range, criteria, [average_range])
Where:
- range is the range of cells to test against the criterion.
- criteria is the condition that defines which cells to average. It can be a number, a text, a logical expression, or a cell reference.
- average_range is the range of cells to average. If omitted, the range argument is used.
The AVERAGEIFS function returns the average of all the cells in a range that meet multiple criteria. The syntax of the AVERAGEIFS function is:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Where:
- average_range is the range of cells to average.
- criteria_range1, criteria_range2, … are the ranges of cells to test against the corresponding criteria.
- criteria1, criteria2, … are the conditions that define which cells to average. They can be numbers, texts, logical expressions, or cell references.
The AVERAGEIFS function works with the AND logic, meaning that only the cells that meet all the criteria are averaged.
Procedures
To use the AVERAGEIF or AVERAGEIFS functions in Excel, follow these steps:
- Select a cell where you want to enter the formula.
- Type =AVERAGEIF( or =AVERAGEIFS( depending on whether you have one or multiple criteria.
- Specify the arguments of the function, separated by commas. For AVERAGEIF, you need to enter the range, the criteria, and optionally the average_range. For AVERAGEIFS, you need to enter the average_range, followed by one or more pairs of criteria_range and criteria.
- Close the parenthesis and press Enter. The formula will return the average of the cells that meet the criteria.
Explanation
To illustrate how the AVERAGEIF and AVERAGEIFS functions work, let us look at some examples using the following table of data:
| Product | Color | Price | Quantity | Sales |
|---|---|---|---|---|
| Apple | Red | 1.5 | 100 | 150 |
| Banana | Yellow | 0.5 | 200 | 100 |
| Cherry | Red | 2 | 50 | 100 |
| Durian | Green | 3 | 20 | 60 |
| Eggplant | Purple | 1 | 40 | 40 |
| Fig | Brown | 2.5 | 30 | 75 |
Example 1: Average price of red products
To find the average price of the products that have the color red, we can use the AVERAGEIF function as follows:
=AVERAGEIF(B2:B7, “Red”, C2:C7)
Where:
- B2:B7 is the range that contains the color values.
- “Red” is the criterion that defines which products to include in the average.
- C2:C7 is the range that contains the price values.
The formula will return 1.75, which is the average of 1.5 and 2, the prices of the red products (apple and cherry).
Example 2: Average sales of products with price greater than 1
To find the average sales of the products that have a price greater than 1, we can use the AVERAGEIF function as follows:
=AVERAGEIF(C2:C7, “>1”, E2:E7)
Where:
- C2:C7 is the range that contains the price values.
- “>1” is the criterion that defines which products to include in the average. The logical operator > means greater than.
- E2:E7 is the range that contains the sales values.
The formula will return 96.25, which is the average of 150, 100, 60, and 75, the sales of the products with price greater than 1 (apple, cherry, durian, and fig).
Example 3: Average quantity of products with color red or yellow
To find the average quantity of the products that have the color red or yellow, we can use the AVERAGEIFS function as follows:
=AVERAGEIFS(D2:D7, B2:B7, “Red”, B2:B7, “Yellow”)
Where:
- D2:D7 is the range of cells to average.
- B2:B7 is the range of cells that contains the color values.
- “Red” and “Yellow” are the criteria that define which products to include in the average.
The formula will return 116.67, which is the average of 100, 200, and 50, the quantities of the products with color red or yellow (apple, banana, and cherry).
Scenario with real data
To demonstrate how the AVERAGEIF and AVERAGEIFS functions can be used in a real-world scenario, let us consider the following table of data that shows the test scores of students in a class:
| Student | Math | English | Science |
|---|---|---|---|
| Alice | 90 | 80 | 85 |
| Bob | 75 | 70 | 65 |
| Carol | 80 | 85 | 90 |
| David | 60 | 65 | 70 |
| Eve | 95 | 90 | 95 |
Example 4: Average math score of students who passed science
To find the average math score of the students who passed science (scored 60 or higher), we can use the AVERAGEIF function as follows:
=AVERAGEIF(C2:C6, “>=60”, B2:B6)
Where:
- C2:C6 is the range that contains the science scores.
- “>=60” is the criterion that defines which students to include in the average. The logical operator >= means greater than or equal to.
- B2:B6 is the range that contains the math scores.
The formula will return 80, which is the average of 90, 75, 80, 60, and 95, the math scores of the students who passed science.
Example 5: Average English score of students who scored 80 or higher in math and science
To find the average English score of the students who scored 80 or higher in both math and science, we can use the AVERAGEIFS function as follows:
=AVERAGEIFS(C2:C6, B2:B6, “>=80”, D2:D6, “>=80”)
Where:
- C2:C6 is the range of cells to average.
- B2:B6 is the range of cells that contains the math scores.
- “>=80” is the criterion that defines which students to include in the average based on their math scores. The logical operator >= means greater than or equal to.
- D2:D6 is the range of cells that contains the science scores.
- “>=80” is the criterion that defines which students to include in the average based on their science scores.
The formula will return 87.5, which is the average of 80 and 95, the English scores of the students who scored 80 or higher in both math and science (Alice and Eve).
