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.

## Leave a Review