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.
How to use AVERAGEIF Function in excel?
  • 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.
How to use AVERAGEIF Function in excel?
  • 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.
How to use AVERAGEIF Function in excel?

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

How to use AVERAGEIF Function in excel?
  • 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.
How to use AVERAGEIF Function in excel?

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.