Array Formulas

It s better to restrict use of this approach to arrays entered within formulas. If you define an array of months of the year elsewhere and use a formula such as =OR(month=array), you must remember to enter the formula as an array formula, that is, by pressing COMMAND+ENTER (Macintosh) or CONTROL+SHIFT+ENTER (Windows). Otherwise the formula returns FALSE unless month=Jan. You can find out much more about array formulas in the following chapter. 

Array formulas are undoubtedly Excel s most powerful formulas. With array formulas, you can accomplish things in Excel that you can t accomplish otherwise. This chapter illustrates the point by means of some case studies. 

Array formulas can simplify worksheets, as illustrated in the following four examples, where absorbance values from a first-order rate process are fitted to the equation A calc = Aoe. The worksheet calculates the sum of squares of residuals, which was minimized by changing Aq and k to obtain the least-squares best fit of the calculated absorbance to the experimental values. The values of Aq and k obtained in this way were 0.85503 and 0.49537, respectively. 

The sum-of-squares calculation can also be done by using an array formula, shown in the second example (Figure 4-2). The formula in cell DIO is an unusual... 

The third example shows how using names for the Aobsd and Aq Ic ranges simplifies the array formula and makes it much more self-documenting. The formula in cell D10 is... 

Figure 4-2. Using an array formula to calculate a sum of squares.

In the same way that a worksheet formula can contain a simple constant, e.g., the value 3 in the formula =3 A1+A2, an array formula can contain an array constant. An array constant is included in a formula by enclosing the array of values in braces. In this case you must type the braces. When you enter the completed array formula by pressing CONTROL+SHIFT+ENTER, Excel automatically provides braces around the whole formula. 

To edit an array formula, simply select any cell in the array. Then edit the formula in the formula bar. When you begin to edit, the braces surrounding the formula will disappear. To re-enter the edited formula, press CONTROL+SHIFT+ENTER. The formula will be entered into all of the cells originally selected for the array. 

You can also create array formulas that return an array of different values in a selected range of cells. Formulas that operate on matrices, described in Chapter 9, are examples of formulas that return an array. 

To use a worksheet formula that returns an array result, you must first select a suitable range of cells, with dimensions (R x C) large enough to accommodate the returned array, then type the formula in the formula bar, and finally enter the formula by pressing CONTROL+SHIFT+ENTER. Excel will indicate that the formula is an array formula by enclosing it in braces and will enter the array formula in all the selected cells. 

It s possible to create an array with three dimensions by entering an array formula in each cell of a rectangular range of cells. The following example illustrates the use of a three-dimensional array to calculate an "error surface" curve such as the one shown in Figure 5-16. The error-square sum, i.e., the sum of the squares of the residuals, S(i/obsd J/calc) for a one-dimensional array of data points, was calculated for each cell of a two-dimensional array of trial values. The "best" values of the independent variables are those which produce the minimum error-square sum. 

Evaluating polynomials or power Series USING Array formulas... 

The formula is an array formula in order to get the correct answer, you must remember to press CONTROL+SHIFT+ENTER. 

Entering an array as an explicit array of constants, as in the preceding example, is not very convenient. Nor does it provide the generality needed for more complicated examples. To generate an array of integers for use in array formulas, use the ROW worksheet function. When used in an array formula, the expression ROW(1 10), evaluates to the array of numbers 1 2 3 4 5 6 7 8 9 10. Thus the formula to evaluate N could have been entered as follows ... 

Often, you ll want to use a variable number of terms in your array formula. You can do this by using INDIRECT. In the following example we want to evaluate N, where cell A1 contains the value for N. In this example let s assume that cell A1 contains 20. We use the formula... 

USING Array formulas to work with lists... 

As well as the use of array formulas themselves, you ll see that the following are invaluable when developing array formulas to operate on lists ... 

In the examples that follow, we will build up the array formula step by step, to show how it works. 

ExceTs COUNTIF and SUMIF worksheet functions can return a value based only on a single criterion. For example, consider a database, the first portion of which is shown in Figure 4-7. You could use COUNTIF to return the number of students in the Chem 1 class whose major is biology, or the number whose year of graduation (YOG) is 96, but you can t use COUNTIF to find out how many students are biology majors and are in the class of 96. You can construct an array formula that will do this. 

To find the number of students whose YOG = 96, we simply have to sum this array of I s and O s. The formula =SUM((YOG=96) 1) is an array formula and you must remember to press CONTROL+SHIFT+ENTER. 

Again, this formula is an array formula, so you must use CONTROL+SHIFT+ENTER to enter it. 

The following expression, used in the formula in cell B3, entered as an array formula, returns the number of duplicate entries in a list ... 

Figure 4-9. Using array formulas to find the number of duplicate or unique entries in a list.

Comparing the two arrays returns TRUE for the unique items only, and multiplying the array of TRUE and FALSE values by 1 produces an array of I s and O s, in this case 1 1 1 0 1 0. Finally, summing the array of I s and O s returns the number of unique items in the list. Once again, the formula is an array formula and must be entered using CONTROL+SHIFT+ENTER. 

Instead of simply returning the number of duplicate entries in a list, you probably want to know which entries are duplicates and where they are located in the list. Part of a list of addresses is shown in Figure 4-10 the complete list contains over 100 entries. The list contains many duplicates an array formula can be used to identify them. 

You can use the LARGE worksheet function in an array formula to sort a list of number values automatically by means of an array formula, rather than manually by using the Sort... command. 

For example, let s imagine that you import a list of numerical data each day and paste them into Sheetl of a workbook. The list must then be sorted in descending order. By means of the following array formula, the list can automatically appear on e.g., Sheet2 of the workbook, sorted in descending order ... 

---