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

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.
Editing an array formula
Editing array formulas differs somewhat from editing normal formulas. In editing an array range, you must treat the range as a single unit and edit it in one operation (corresponding to the way in which the array formula was entered). This means that you can’t edit, clear, move, insert, or delete indi- vidual cells in the array range. If you try, Excel will display an Alert dialog box stating “You cannot change part of an array.”
To edit the contents of an array formula, select a cell in the array range
and then activate Edit mode by clicking the formula or the Formula bar or pressing F2. When you do this, Excel displays the contents of the array for- mula without the customary braces. The program also outlines the ranges referred to in the array formula in the cells of the worksheet in different colors that match those assigned to the range addresses in the edited formula on the Formula bar. After you make your changes to the formula contents, you must remember to press Ctrl+Shift+Enter to enter your changes and have Excel enclose the array formula in braces once again.
If you want to convert the results in an array range to their calculated values, select the array range and click the Copy button on the Ribbon’s Home tab or press Ctrl+C. Then, without changing the selection, choose the Paste Values option from the Paste button’s drop-down menu (or press Alt+HVV). As soon as you convert an array range to its calculated values, Excel no longer treats the cell range as an array.
Range Names in Formulas
Thus far, all the example formulas in this chapter have used a combination of numerical constants and cell references (both relative and absolute and using the A1 and R1C1 notation). Although cell references provide a conve- nient method for pointing out the cell location in the worksheet grid, they are not at all descriptive of their function when used in formulas. Fortunately, Excel makes it easy to assign descriptive names to the cells, cell ranges, constants, and even formulas that make their function in the worksheet much more understandable.
Range Names in Formulas 323
Book III Chapter 1
Building Basic Formulas