|
Many Excel spreadsheet tables use an original formula that you copy to adjacent cells by using relative cell references (sometimes referred to as a one-to-many copy). In some cases, you can build the original formula so that Excel performs the desired calculation not only in the active cell, but also in all the other cells to which you would normally copy the formula. You do this by creating an array formula. An array formula is a special formula that operates on a range of values. If a cell range supplies this range (as is often the case), it is referred to as an array range. If this range is supplied by a list of numerical values, they are known as an array constant.
Although the array concept may seem foreign at first, you are really quite familiar with arrays because the column-and-row structure of the Excel worksheet grid naturally organizes your data ranges into one-dimensional and two-dimensional arrays (1-D arrays take up a single row or column, whereas 2-D arrays take up multiple rows and columns).
To get an idea of how you build and use array formulas in a worksheet, consider the sample worksheet shown in Figure 1. This worksheet is designed to compute the biweekly wages for each employee. It will do this by multiplying 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
Figure 1: Building an array formula to calculate hourly wages for the first pay period.
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:
1. Make cell R10 the active cell, and then select the array range R10:R13 and type = (equal sign) to start the array formula.
You always start an array formula by selecting the cell or cell range where the results are to appear. Note that array formulas, like standard formulas, begin with the equal sign.
2. Select the range A4:A7 that contains the hourly rate for each employee as shown, type an * (asterisk for multiplication), and then select the range R4:R7 that contains the total number of hours worked during the first pay period.
3. Press Ctrl+Shift+Enter to insert an array formula in the array range.
When you press Ctrl+Shift+Enter to complete the formula, Excel inserts braces around the formula and copies the array formula {=A4:A7*R4:R7} into each of the cells in the array range R10:R13.
 | When entering an array formula, you must remember to press Ctrl+Shift+Enter instead of just the Enter key because this special key combination that tells Excel that you are building an array formula, so that the program encloses the formula in braces and copies it to every cell in the array range. Also, don't try to create an array formula by editing it on the Formula bar and then inserting curly braces — that doesn't cut it. The only way to create an array formula is by pressing Ctrl+Shift+Enter to complete the formula entry. |
Figure 2 shows you the February wage table after completing all the array formulas in three ranges: R10:R13, AI10:AI13, and AJ10:AJ13. In the second cell range, AI10:AI13, the following array formula calculates the hourly wages for the second pay period in February:
{=A4:A7*AI4:AI7}
Figure 2: The hourly wage spreadsheet after entering all three array formulas.
In the third cell range, AJ10:AJ13, the following array formula calculates the total wages paid to each employee in February 2003:
{=R10:R13+AI10:AI13}
| When you enter an array formula, the formula should produce an array with the same dimensions as the array range that you selected. If the resulting array returned by the formula is smaller than the array range, Excel expands the resulting array to fill the range. If the resulting array is larger than the array range, Excel doesn't display all the results. When expanding the results in an array range, Excel considers the dimensions of all the arrays used in the arguments of the operation. Each argument must have the same number of rows as the array with the most rows and the same number of columns as the array with the most columns. |
| 
