Microsoft Office Tutorials and References
In Depth Information
This chapter contains many examples of array formulas. Keep in mind that you press Ctrl+Shift+Enter to enter an
array formula. Excel places curly brackets around the formula to remind you that it's an array formula. The array
formula examples shown here are surrounded by curly brackets, but you should not enter the brackets because
Excel will do that for you when the formula is entered.
Figure 15-1: An array formula can sum a range of values, even if the range contains errors.
The following array formula, in cell D13, overcomes this problem and returns the sum of the values, even if the
range contains error values:
This formula works by creating a new array that contains the original values but without the errors. The IF func-
tion effectively filters out error values by replacing them with an empty string. The SUM function then works
on this “filtered” array. This technique also works with other functions, such as AVERAGE, MIN, and MAX.
The IFERROR function was introduced in Excel 2007. Following is a modified version of
the formula that's compatible with older versions of Excel: