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

Adding Array Formulas 319
✦ The fourth time you press F4, Excel changes the mixed reference where the row is relative and the column is absolute back to a relative reference ($C10 to C10).
If you bypass the type of cell reference that you want to use, you can return to it by continuing to press F4 until you cycle through the variations again to reach the one that you need.
Adding Array Formulas
As noted previously in this chapter, many 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.)
Figure 1-9 illustrates a couple of two-dimensional arrays with numerical entries of two different sizes. The first array is a 3 x 2 array in the cell range B2:C4. This array is a 3 x 2 array because it occupies three rows and two columns. The second array is a 2 x 3 array in the cell range F2:H3. This array is a 2 x 3 array because it uses two rows and three columns.
Figure 1-9:
Worksheet with two different sizes of arrays.
Book III Chapter 1
Building Basic Formulas