Microsoft Office Tutorials and References

In Depth Information

**Array Formulas**

Figure 3-6:
Cell B1 contains an array formula that returns the total number of characters contained in range

A1:A5. Notice the brackets in the formula bar.

To demonstrate how an array formula can occupy more than one cell, create the worksheet

shown in the figure and then try these steps:

1.

Select the range B1:B5.

2.

Type the following formula:

=LEN(A1:A5)

3.

Press Ctrl+Shift+Enter.

The preceding steps enter a single array formula into five cells. Enter a SUM formula that adds

the values in B1:B5, and you’ll see that the total number of characters in A1:A5 is 29.

Here’s the key point: It’s not necessary to actually
display
those five array elements. Rather, Excel

can store the array in memory. Knowing this, you can type the following single array formula in

any blank cell (
Remember:
Don’t type the curly brackets and make sure that you enter it by

pressing Ctrl+Shift+Enter):

{=SUM(LEN(A1:A5))}

This formula essentially creates a five-element array (in memory) that consists of the length of

each string in A1:A5. The SUM function uses this array as its argument, and the formula returns 29.

An array formula calendar

Figure 3-7 shows a worksheet set up to display a calendar for any month. (Change the month,

and the calendar updates.) Believe it or not, the calendar is created with a single array formula

that occupies 42 cells.

The array formula, entered in the range B5:H10, is

{=IF(MONTH(DATE(YEAR(B3),MONTH(B3),1))<>MONTH(DATE(YEAR(B3),

MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),MONTH(B3),1))-1)

+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1),””,

DATE(YEAR(B3),MONTH(B3),1)-(WEEKDAY(DATE(YEAR(B3),

MONTH(B3),1))-1)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7}-1)}