The AVERAGEIFS function

Welcome back to our usual blog of Excel functions from A to Z. Today we look at the AVERAGE YES function.

The AVERAGEIFS function

Returns the average (arithmetic average) of all cells that meet multiple criteria. Is the like “average” of SUMIFS.

The AVERAGE YES The function measures what is known as “central tendency”, which is the location of the center of a group of numbers in a statistical distribution. There are three common measures of central tendency:

• Average: the arithmetic mean, calculated by adding a group of numbers and then dividing by the count of those numbers. As an example, the average of 2, 3, 3, 5, 7 Y 10 it is 30 divided by 6, What is it 5
• Median: the middle number of a group of numbers; In other words, half of the numbers have values ​​greater than the median and half of the numbers have values ​​less than the median. As an example, the median of 2, 3, 3, 5, 7 Y 10 it is 4. If the number of values ​​in the selection is an even number, the median is set as the midpoint between the two middle numbers.
• Way: the most frequently occurring number in a group of numbers. As an example, the fashion of 2, 3, 3, 5, 7 Y 10 it is 3.

For a symmetric distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different.

The AVERAGE YES The function uses the following syntax to operate:

The AVERAGE YES The function has the following arguments:

• average range: This is required. One or more cells to average, including numbers, Names, arrays or references containing numbers
• criterion_range1: this is necessary, consisting of one or more cells to be used for the criterio1 and can include numbers or names, arrays or references containing numbers
• criterion_range2,…: until 126 more ranks (with associated criteria) are optional
• criterio1: this is also necessary. The criterion in the form of a number, expression, cell or text reference that sets which cells are averaged. As an example, the criteria can be expressed as 32, “32”, “> 32”, “apples”, B4 O “> =” &B4.
• criterio2…: this is necessary if the respective optional criterion_range it is invoked. The syntax must be similar to that used for criterio1.

It should be noted at the same time that:

• And average range is a blank or text value, AVERAGE YES return the # DIV0! error value
• If a cell in a criteria range is empty, AVERAGE IF treproduces it as a zero value
• Cells in the range that contain TRUE are evaluated as 1; cells in range containing FALSE examine as zero
• Each cell in average range used in average calculation only if all specified corresponding criteria are TRUE for that cell
• Unlike the range and criterion arguments in the AVERAGE YES function, in AVERAGE YES every criterion_range must be the same size and shape as average range
• If the cells in average range cannot be translated into numbers, AVERAGEIFS devuelve # DIV0! error value
• If there are no cells that meet all the criteria, AVERAGE YES return the # DIV / 0! error value
• You can use wildcard characters, the question mark (?) And the asterisk

, in criteria. A question mark matches any character; an asterisk matches any sequence of characters. If you want to find a real question mark or asterisk, write a tilde (~) before character.