Excel Tip Average Logic
Our last installment we discussed the logical-mathematical functions SUMIF and COUNTIF. Here we continue that discussion with the functions AVERAGEIF and IFERROR, and we will touch briefly on the new functions introduced in Excel 2010, SUMIFS, AVERAGEIFS, and COUNTIFS.
As a quick review, the syntax for the SUMIF and COUNTIF functions discussed in the May article are:
SUMIF(range, criteria, sum_range)
AVERAGEIF and IFERROR Functions
A handy logical function is AVERAGEIF, which will average the cells within a range that meet a test or criteria. The syntax for AVERAGEIF works much the same way as SUMIF:
AVERAGEIF(range, criteria, average_range)
range- range of cells to be evaluated by a criteria
criteria- criteria in the form of a number, expression, cell reference, text, or function
average_range- range of cells to average by the criteria
Table 1 [AVERAGEIF and IFERROR functions]
The AVERAGEIF formula in cell B9 in Table 1 [=AVERAGEIF($A$2:$A$6,A9,$B$2:$B$6)] says:
IF any of the cells in A2:A6 meet the criteria in cell A9, then average the corresponding values in range B2:B6. The error message #DIV/0! in cell B12 occurs because the Fund 204040D16 does not exist in the source data table, and the averaging formula is trying to divide by 0 or nothing (does not exist). Note that the dollar signs $ in the formula create Absolute References, which do not change as the formula is copied down.
Use the IFERROR function to avoid the formula returning an error message if the specific criteria being searched does not exist, like the error shown in cell B12. The syntax for IFERROR is:
value- a formula that is checked for an error, and if none, then returns the result
value_if_error- value returned if formula evaluates to an error (i.e., #N/A, #VALUE!, #REF!,
#DIV/0!, #NUM!, #NAME?, or #NULL)
Looking back at the formula in cell B15 in Table 1 [=IFERROR(AVERAGEIF($A$2:$A$6,A15,$B$2:$B$6),0)] says:
IF the formula [AVERAGEIF($A$2:$A$6,A15,$B$2:$B$6)] returns an error message (e.g., #DIV/0!), then return 0, otherwise return the value of the formula [AVERAGEIF($A$2:$A$6,A15,$B$2:$B$6)].
The IFERROR function can be used with any function or formula that could return an error message, such as VLOOKUPs. The downside to IFERROR is that it is a blunt instrument that will trap almost all errors. For example, in the above formula we would want to trap the #DIV/0! error when a Fund does not exist in the source data table. However, we would still want to see the #NAME? error if something in the argument being tested is misspelled, but the #NAME? error will not be displayed when using the IFERROR function. Good topic for a future newsletter article.
SUMIFS, AVERAGEIFS and COUNTIFS Functions
The SUMIF, AVERAGEIF, and COUNTIF functions work with only one column of criteria. A workaround to Excel functions that use only one column was addressed in the Excel Tips column titled “Many to One” in the February 2016 AVC Newsletter. However, Microsoft saw the need and in Excel 2010 introduced three new logical functions that can use multiple columns of criteria: SUMIFS, AVERAGEIFS, and COUNTIFS.
The syntax for these new functions is different (and the arguments are in a different order) than the earlier functions allowing only one column and criteria to test. For brevity, we will just show the syntax for the SUMIFS function:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range- a range of cells to sum
criteria_range1- a range of cells to be evaluated by criteria1
criteria1- criteria1 in the form of a number, expression, cell reference, text, or function
criteria_range2, criteria2- additional ranges and associated criteria are optional (up to 127
range/criteria pairs are allowed).
If you have Excel 2010 or 2013, then you might consider switching to the functions SUMIFS, AVERAGEIFS, and COUNTIFS.
You can use the function AVERAGEIF (or AVERAGEIFS) to average cells in a range based on a criteria and the function IFERROR to trap any errors. It’s error-free average logic! Formula on, dudes…