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.
