# AVERAGEIF Function in Excel

In this post, you’ll learn about AVERAGIF Function, its syntax and how to use AVERAGEIF Function in excel spreadsheet.

## What is an AVERAGEIF Function?

The AVERAGEIF Function in excel is a statistical function, which calculates the average of a given range of cells by a specific criterion(standard).

AVERAGEIF calculates the central tendency, which is the location of center of groups of numbers in a statistical distribution. AVERAGEIFS Function calculates the average of cells that meet multiple criteria. AVERAGEIF Function was introduced in the year MS EXCEL 2007.

AVERAGEIF Function criteria can include logical operations (>, <, <>, =) and wildcards (*, ?) for partial matching.

#### Syntax:

`= AVERAGEIF (range, criteria, [average_range])`

#### Parameters:

• = – built-in function.
• AVERAGEIF() – function’s name.
• Range(required argument) – it is range of one of more cells that we want to calculate the average.

The range arguments(values) can be numbers or arrays, or reference that contain numbers.

• Criteria (required argument) – it determines the average of the cell.
• Average_range (optional argument) – it is an actual set of cells . If a user omits it, the function will use the range given.

AVERAGEIF is in a group of eight functions in Excel that split logical criteria into parts (range + criteria). As a result, the syntax used in constructing criteria is different. AVERAGEIF Function can only apply to single condition.

##### Note:
• Ignores cells in range that contain TRUE and FALSE
• Ignores Empty cells in range and average_range when calculating averages.
• AVERAGEIF returns #DIV/0!(error) if no cells in range meet criteria.
• Average_range do not want to be the same size as the range.
• The starting point is in the top left cell in the average_range , and cells corresponds to cells in range are averaged.
• When the criteria is empty, AVERAGEIF Function treats it as 0 value.
• The use of wildcard characters such as asterisk (*),the tilde (~) in the function as criteria, greatly enhance the search.

## How to use AVERAGEIF Function in excel?

AVERAGEIF Function is a built-in function which is a worksheet function in excel.

##### DOUBLE QUOTES (“”):
• Text values is enclosed within double quotes, but numbers are not. If logical operator is included with a number, the number and operator must be enclosed in double quotes.
• Value from another cell – value from another cell is included using catenation.
• WILLCARDS:

The wildcards characters question mark (?), asterisk (*), or tilde (~) can used in criteria.

• The question mark (?) matches any single characters.
• The asterisk (*) matches any sequences of characters.
• The tilde (~) is an escape character it allow you to find the literal wildcard.
##### Step 1:

Open the workbook in your Microsoft Excel.

##### Step 2:

Enter the data, in the workbook.

##### Step 3:

In the new cell, give the formula of the function or the syntax . always start with ‘ =’ for every functions, AVERAGEIF to initiate functions name, followed by open parenthesis. Use the below examples to calculate the AVERAGEIF function.

#### Examples:

• With the parenthesis open in the cell, in this you can calculate the average of all values in the range B1:B7, if the corresponding cells in the range A1:A7 contain exactly apple.
• Close the parenthesis and press Enter to get the calculated results.
• Follow the above steps. With the parenthesis open, in this example, you can calculate the average that do not contain exactly Banana by using logical operation <>.
• Close the parenthesis, at the end and press Enter to get the results.
• Follow the above steps, with the parenthesis open in this example you can calculate the average of all values in the range B1:B7 corresponding cells in the A1:A7 contain a series of zero or more characters + berry. An asterisk * matches zero or more character.
• Close the parenthesis at the end.
• Press Enter to get the calculated results.

If you didn’t give * asterisk within the above example, it shows an error.

• Follow the above steps, in this example we can calculate the average of all range B1:B7 f the corresponding cells in the range A1:A7 contain exactly four characters.
• A question mark(?) matches exactly one character. Close the parenthesis at the end.
• Press Enter to get the calculated results.