Microsoft Office Tutorials and References
In Depth Information
Using array formulas
Rather than add the same formula to multiple cells one cell at a time, you can add a
formula to every cell in the target range at once by creating an array formula. To create an
array formula, you enter the formula’s arguments and press Ctrl+Shift+Enter to identify the
formula as an array formula. To calculate package insurance rates for values in the cell range
B4:B6 and the rate in cell B1, you would select a range of cells with the same shape as the
value range and enter the formula =B1*B4:B6 . In this case, the values are in a three-cell
column, so you must select a range of the same shape, such as C4:C6.
3
IMPORTANT If you enter the array formula into a range of the wrong shape, Excel displays
duplicate results, incomplete results, or error messages depending on how the target range
differs from the value range.
When you press Ctrl+Shift+Enter, Excel creates an array formula in the selected cells. The
formula appears within a pair of curly braces to indicate it is an array formula. In this case,
the formula in cells C4:C6 is {=B1*B4:B6} .
IMPORTANT You can’t add curly braces to a formula to make it an array formula—you must
press Ctrl+Shift+Enter to create it.
In addition to creating an array formula that combines a single cell’s value with an array,
you can create array formulas that use two separate arrays. For example, Consolidated
Messenger might establish a goal to reduce sorting time in each of four distribution
centers.
Search JabSto ::




Custom Search