Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

About the examples in this chapter

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 C13, overcomes this problem and returns the sum of the

values, even if the range contains error values:

{=SUM(IFERROR(C4:C10,””))}

This formula works by creating a new array that contains the original values but without the

errors. The IF function 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:

{=SUM(IF(ISERROR(C4:C10),””,C4:C10))}