AVERAGEIF Function: Calculating Conditional Average in Excel

Buzz

Content

Syntax

Note

Example

AVERAGEIF() function returns the average of selected cells that meet the specified condition.

Syntax

=AVERAGEIF(range,criteria,average_range)

Where:

- range: represents one or more cells to calculate the average, which can be numbers, named ranges, arrays, or references containing numbers.

- criteria: is the condition in the form of a number, expression, cell reference, or text that specifies which cells to average.

- average_range: collection of cells to actually calculate the average, if average_range is omitted, then range will be used to calculate the average.

Note

- Empty cells in average_range are ignored.

- Logical values (TRUE or FALSE) in range are also ignored.

- If range is empty or contains text, AVERAGEIF() function will return an error.

- If criteria contains an empty cell, it will be treated as 0.

- If no cells in the range meet the criteria, AVERAGEIF() function reports an error.

- You can utilize wildcard characters such as question mark (?) to substitute for any single character, asterisk (*) to replace any string in the criteria. When the criteria contain ? or *, type ~ before them.

- Average_range and range do not necessarily have to be of the same size. The actual cells averaged will use the top-left cell of average_range as the starting point, including additional cells corresponding to the size of the range. For a clearer understanding, refer to the example below:

Example

Example 1:

Example 2:

With these two examples, you will gain a clearer understanding of how to use the AVERAGEIF() function in Excel. Wishing you success!

The content is developed by the Mytour team with the aim of customer care and solely to inspire travel experiences. We do not take responsibility for or provide advice for other purposes.

If you find this article inappropriate or containing errors, please contact us via email at [email protected]

Frequently Asked Questions

What is the syntax for the AVERAGEIF() function in Excel?

The syntax for the AVERAGEIF() function in Excel is =AVERAGEIF(range, criteria, average_range). Here, 'range' specifies the cells to evaluate, 'criteria' sets the condition for averaging, and 'average_range' is the set of cells to average, which is optional.

How does the AVERAGEIF() function handle empty cells and logical values?

The AVERAGEIF() function ignores empty cells in the average_range and logical values (TRUE or FALSE) in the range. However, if the range is empty or contains text, it will return an error.

Can you use wildcard characters in the criteria for AVERAGEIF()?

Yes, you can use wildcard characters in the criteria for the AVERAGEIF() function. The question mark (?) substitutes for any single character, while the asterisk (*) replaces any string. To include these characters literally, precede them with a tilde (~).

What happens if no cells meet the criteria in AVERAGEIF()?

If no cells meet the criteria specified in the AVERAGEIF() function, it will report an error. This indicates that there are no applicable cells to calculate the average based on the provided condition.