Excel: AVERAGEA, AVERAGEIF and AVERAGEIFS Functions

AVERAGEA

AVERAGEA function in Excel returns the average of a group of mixed (text & number) values. The AVERAGEA function returns the average of a group of supplied values. The key difference between AVERAGEA and AVERAGE is AVERAGEA will also evaluate the logical values TRUE and FALSE, and numbers represented as text when when they appear in cell references, whereas AVERAGE just skips cells contains non-numeric values.

AVERAGEIF

AVERAGEIF function in Excel calculates the average of numbers in a range that meet a criteria. AVERAGEIF criteria can include wildcards (?,*) and logical operators (>,<,<>,=) for partial matching. 

Arguments of AVERAGEIF function:

• range - One or more cells, including numbers or names, arrays, or references. 
• criteria - A number, expression, cell reference, or text.
• average_range - The cells to average.

AVERAGEIFS

AVERAGEIFS function in Excel calculates the average of numbers in a range that meet one or more then one criteria. The criteria used for AVERAGEIFS can include wildcards (?,*) and logical operators (>,<,<>,=) for partial matching. The first range given to AVERAGEIFS is the range containing numbers to average. Criteria are supplied in pairs (range & criteria) and only the first pair is mandatory. The criteria given to AVERAGEIFS are applied with AND logic all criteria must be true for a number to be included in the average. For each additional criteria, supply another range/criteria pair.

Arguments of AVERAGEIFS function:

• average_range - The range to average.
• range1 - The first range to evaulate.
• criteria1 - The criteria to use on range1.
• range2 - The second range to evaluate.
• criteria2 -The criteria to use on range2, and so on...