AVERAGEIF function

The AVERAGEIF function in Excel calculates the average (arithmetic mean) of all the cells in a range that meet a specific condition or criteria.

Syntax

=AVERAGEIF(range, criteria, [average_range])

• range: The range of cells that you want to evaluate against the criteria.
• criteria: The condition that determines which cells in the range will be included in the average calculation.
• average_range (optional): The actual cells to average. If omitted, Excel averages the values in the range.

Description

• AVERAGEIF calculates the average of numbers that meet a single condition.
• The criteria can be a number, expression, cell reference, or text string (e.g., ">10", "<=20", "Apple").
• If average_range is not provided, Excel uses range for both the criteria and averaging.
• Cells that contain text or empty cells are ignored.

Example 1: Basic Usage

To calculate the average of numbers greater than 10 in the range A1:A5, use:

=AVERAGEIF(A1:A5, ">10")

Suppose cells A1:A5 contain:

• A1 = 5
• A2 = 12
• A3 = 20
• A4 = 8
• A5 = 15

Result:

The function will return 15.67, because the numbers greater than 10 are 12, 20, 15:

$$\frac{12 + 20 + 15}{3} = 15.67$$

Example 2: Using a Text Condition

If you want to calculate the average of numbers in B1:B5 where the corresponding values in A1:A5 contain “Apple”, use:

=AVERAGEIF(A1:A5, "Apple", B1:B5)

Suppose cells contain:

• A1 = “Apple”, B1 = 10
• A2 = “Orange”, B2 = 15
• A3 = “Apple”, B3 = 20
• A4 = “Banana”, B4 = 25
• A5 = “Apple”, B5 = 30

Result:

The function will return 20, because the values in B1, B3, B5 corresponding to “Apple” are 10, 20, 30:

$$\frac{10 + 20 + 30}{3} = 20$$

Example 3: Criteria with Cell Reference

If the condition is based on a cell reference, you can use that reference in the criteria. For example, if you want to average numbers in A1:A5 that are greater than the value in B1, you can write:

=AVERAGEIF(A1:A5, ">" & B1)

Suppose:

• B1 = 10
• A1:A5 contains 5, 12, 20, 8, and 15.

Result:

This will average the numbers 12, 20, 15, which are greater than 10, returning 15.67.

Example 4: Using Wildcards in Criteria

AVERAGEIF also allows the use of wildcards in the criteria when working with text:

• * (asterisk): Matches any number of characters.
• ? (question mark): Matches a single character.

For example, if you want to average the values in B1:B5 where the text in A1:A5 starts with “App”, use:

=AVERAGEIF(A1:A5, "App*", B1:B5)

This will include all entries where the text begins with “App” (e.g., “Apple”).

Handling Errors

• If no cells match the criteria, AVERAGEIF returns #DIV/0! because there are no values to average.
• Ensure the criteria are correct and that the ranges contain the appropriate data.

Practical Use Cases

• AVERAGEIF is useful in situations where you need to analyze data based on a specific condition, such as:
  • Calculating the average sales for a specific product category.
  • Finding the average score for students who scored above a certain threshold.

Important Notes

• Text and blank cells are ignored.
• If the criteria is a text string or an expression, it should be enclosed in quotation marks.
• Wildcards (* and ?) can be used in the criteria to match partial text.

The AVERAGEIF function helps you efficiently compute averages for data subsets that meet specific conditions.