Welcome back
In today's lesson, we will soon learn about a very
important Excel function with more than one example for clarification
AVERAGEIFS function in Excel
The AVERAGEIFS function is one of the important functions
in Microsoft Excel, as it calculates the rate based on several conditions.
We are here in the world of office website. We will
explain the AVERAGEIFS function in detail
This article describes the formula syntax and usage of
the AVERAGEIFS function in Microsoft Excel.
Description
function
This machine returns the mean (arithmetic mean) of all
cells that match multiple criteria.
Syntax
function
AVERAGEIFS (average_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)
The syntax for the AVERAGEIFS function has the following
arguments:
Average_range is required. One or more cells to average, including
numbers, names, arrays, or references containing numbers.
Criteria_range1, criteria_range2 ,
Criteria_range1 is required, the following criteria_range arguments are
optional. Ranges 1 to 127 in which the associated criteria are evaluated.
... Criteria1, criteria2 , Criteria1 is
required, the following criteria are optional. Criteria 1 through 127 in the
form of a number, expression, cell reference, or text to identify the cells to
be averaged. For example, criteria can be expressed as 32, '32', ">32',
'apples', or B4.
Important
Notes
·
If
average_range is a null or text value, AVERAGEIFS returns #DIV0! error value.
·
If
a cell in a criteria range is empty, AVERAGEIFS treats it as 0.
·
Cells
in a range containing TRUE evaluate as 1; Whereas, cells in a range containing
FALSE evaluate as 0 (zero).
·
Each
cell in average_range is used in calculating the average only if all criteria
specified for that cell match.
Unlike the range and criteria arguments in the AVERAGEIF
function, in AVERAGEIFS each criteria_range value must be the same size and
shape as sum_range values.
If average_range cells can be translated into numbers,
AVERAGEIFS returns #DIV0! error value.
If no cells meet all criteria, AVERAGEIFS returns #DIV/0!
.
You can use wildcards, a question mark (?), and an
asterisk (*) in the criteria. The question mark matches any single character;
While the asterisk matches any character sequence. If you want to search for an
actual question mark or asterisk, type the tilde (~) character before the
character.