Page 338 - Excel 2013 All-in-One For Dummies

320 Adding Array Formulas
If you were to list the values in the first 3 x 2 array as an array constant in a formula, they would appear as follows:
{1,4;2,5;3,6}
Several things in this list are noteworthy. First, the array constant is enclosed in a pair of braces ({}). Second, columns within each row are sepa- rated by commas (,) and rows within the array are separated by semicolons (;). Third, the constants in the array are listed across each row and then down each column and not down each column and across each row.
The second 2 x 3 array expressed as an array constant appears as follows:
{7,8,9;10,11,12}
Note again that you list the values across each row and then down each column, separating the values in different columns with commas and the values in different rows with a semicolon.
The use of array formulas can significantly reduce the amount of formula copying that you have to do in a worksheet by producing multiple results throughout the array range in a single operation. In addition, array formulas use less computer memory than standard formulas copied in a range. This can be important when creating a large worksheet with many tables because it may mean the difference between fitting all your calculations on one work- sheet and having to split your model into several worksheet files.
Building an array formula
To get an idea of how you build and use array formulas in a worksheet, con- sider the sample worksheet shown in Figure 1-10. This worksheet is designed to compute the biweekly wages for each employee. It will do this by multi- plying each employee’s hourly rate by the number of hours worked in each pay period. Instead of creating the following formula in cell R10, you must copy down the cells R11 through R13:
=A4*R4
You can create the following array formula in the array range:
={A4:A7*R4:R7}
This array formula multiplies each of the hourly rates in the 4 x 1 array in
the range A4:A7 with each of the hours worked in the 4 x 1 array in the range R4:R7. This same formula is entered into all cells of the array range (R10:R13) as soon as you complete the formula in the active cell R10. To see how this is done, follow along with the steps required to build this array formula: