Читать книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller - Страница 31

In addition to Excel’s built-in array formulas, you can create your own. (Again, not on the iPad.) To help things along, you can incorporate named arrays.

Figure 2-22 shows two named arrays, X and Y, in columns C and D, respectively. X refers to C2 through C5 (not C1 through C5), and Y refers to D2 through D5 (not D1 through D5). XY is the column header for column F. Each cell in column F stores the product of the corresponding cell in column C and the corresponding cell in column D.

FIGURE 2-22: Two named arrays and an array formula.

An easy way to enter the products, of course, is to set F2 equal to C2*D2 and then autofill the remaining applicable cells in column F.

Just to illustrate array formulas, though, follow these steps to work on the data in the worksheet (refer to Figure 2-22):

1 Select the cell to start the output array.That would be F2. (Figure 2-21 shows the selected cell.)

2 Into the selected cell, type the formula.The formula here is =X * Y

3 Press Enter.The answers appear in F2 through F5, as Figure 2-23 shows.

FIGURE 2-23: The results of the array formula =X * Y.

When you name a range of cells, make sure that the named range does not include the cell with the name in it. If it does, an array formula like {=X * Y} tries to multiply the letter X by the letter Y to produce the first value, which is impossible and results in the exceptionally ugly #VALUE! error.