Array Formulas


In Excel terminology, an array is a collection of cells or values that is operated on as a group . An array formula is a special type of formula that works with arrays. An array formula can produce a single result, or it can produce multiple results - with each result displayed in a separate cell .

For example, when you multiply a 1 — 5 array by another 1 — 5 array, the result is a third 1 — 5 array. In other words, the result of this kind of operation occupies five cells; each element in the first array is multiplied by each corresponding element in the second array to create five new values, each getting its own cell. The array formula that follows multiplies the values in A1:A5 by the corresponding values in B1:B5. This array formula is entered into five cells simultaneously :

 {=A1:A5*B1:B5} 
Note  

You enter an array formula by pressing Ctrl+Shift+Enter. To remind you that a formula is an array formula, Excel surrounds it with curly braces in the formula bar. When I present an array formula in this book, I enclose it in curly braces to distinguish it from a normal formula. Don't enter the braces yourself.

An array formula example

Excel's array formulas enable you to perform individual operations on each cell in a range in much the same way that a programming language's looping feature enables you to work with elements of an array. If you've never used array formulas before, this section will get your feet wet with a hands-on example.

Figure 3-6 shows a worksheet with text in A1:A5. The goal of this exercise is to create a single formula that returns the sum of the total number of characters in the range. Without the single formula requirement, you would write a formula with the LEN function, copy it down the column, and then use the SUM function to add the results of the intermediate formulas.

image from book
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 this:

  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.

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

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

The companion CD-ROM contains a workbook with the calendar example, as well as several additional array formula examples. The file is named image from book  array formula examples.xlsx .

Array formula pros and cons

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

  • They can sometimes use less memory.

  • They can make your work much more efficient.

  • They can eliminate the need for intermediate formulas.

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

A few disadvantages of using array formulas are the following:

  • Using many complex array formulas can sometimes slow your spreadsheet recalculation time to a crawl.

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

  • You must remember to enter an array formula with a special key sequence (by pressing Ctrl+Shift+Enter).




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net