Microsoft Office Tutorials and References

In Depth Information

**Array Formulas**

The formula returns date serial numbers, and you need to format the cells to display the day

number only by using a custom number format (“d”).

Figure 3-7:
A single multicell array formula is all it takes to make a calendar for any month in any year.

The companion CD-ROM contains a workbook with the calendar example, as well as

several additional array formula examples. The file is named
array formula

examples.xlsx
. In addition, you’ll find a workbook named
yearly calendar.xlsx
that

displays a calendar for a complete year.

Array formula pros and cons

The advantages of using array formulas rather than single-cell formulas include the following:

h
They can sometimes use less memory.

h
They can make your work much more efficient.

h
They can eliminate the need for intermediate formulas.

h
They can enable you to do things that would be difficult or impossible otherwise.

A few disadvantages of using array formulas are the following:

h
Using many complex array formulas can sometimes slow your spreadsheet recalculation

time to a crawl.

h
They can make your worksheet more difficult for others to understand.

h
You must remember to enter an array formula with a special key sequence (by pressing

Ctrl+Shift+Enter).