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)}

Search JabSto ::

Custom Search