AVERAGEIF

Find the average of numbers in Excel
Returns the average for the cells specified by a given criterion

Rational

Find the average of numbers based on a rule.

Description

The Excel AVERAGEIF function return an average figure based on given criteria.
For example, suppose that a list of digits is in a column and user want to average the number range that is more than 5, the formula can be =AVERAGEIF({6;8;10;3;5},”>5″) average numbers that are more than 5 and return 8.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Parameters

range – The range of cells to evaluate against the criteria
criteria – The criteria to apply for average on the range
average_range [optional] – The range from which to average values. If average_range is omitted, the range is used.

Return Value

Average of numbers based on given criteria.

Key Notes

  • The AVERAGEIF function calculates the average (arithmetic mean) a set of numbers (range) in the arguments determined as per the given criteria (criteria).
  • To calculate the average with multiple conditions use the AVERAGEIFS function.
  • Arguments can be supplied as numbers, ranges, named ranges, or cell references that contain a numerical value.
  • AVERAGEIF returns the #DIV/0! error value in case no value meets the criteria.
  • Ignore Empty cells in range and average_range.
  • For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”. The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.

Keep Reading Similar Functions:

Find the average of numbers in Excel

AVERAGE

The AVERAGE function returns an average of numbers positioned in one or multiple ranges, =AVERAGE(5,15,30) add numbers and divide by 3, and returns 16.67.

Read More »
Find the average of a cell range based on multiple criteria in Excel

AVERAGEIFS

The AVERAGEIFS function returns an average number based on one or multiple given criteria, sum adjacent cells that meet given conditions.

Read More »