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)

COUNTIF(range, criteria)             

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]

A better approach traps the error in cell B12 with IFERROR   This is our source data table (think 100k rows)   Average of Amount Paid in source data table by Fund

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:

 IFERROR(value, value_if_error)

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…