One of Excel’s most interesting (and most powerful) features is its ability to work with arrays in a formula. When you understand this concept, you’ll be able to create elegant formulas that appear to perform magic. This chapter introduces the concept of arrays and is required reading for any-one who wants to become a master of Excel formulas. Chapter 15 continues with lots of useful examples.
Introducing Array Formulas
If you do any computer programming, you’ve probably been exposed to the concept of an array. An array is a collection of items operated on collectively or individually. In Excel, an array can be one-dimensional or two-dimensional. These dimensions correspond to rows and columns. For example, a one-dimensional array can be stored in a range that consists of one row (a horizontal array) or one column (a vertical array). A two-dimensional array can be stored in a rectangular range of cells. Excel doesn’t support three-dimensional arrays (although its VBA programming language does).
As you’ll see, though, arrays need not be stored in cells. You can also work with arrays that exist only in Excel’s memory. You can then use an array formula to manipulate this information and return a result. An array formula can occupy multiple cells or reside in a single cell.
This section presents two array formula examples: an array formula that occupies multiple cells, and another array formula that occupies only one cell.
A multicell array formula
Figure 14-1 shows a simple worksheet set up to calculate product sales. Normally, you would cal-culate the value in column D (total sales per product) with a formula such as the one that follows, and then copy this formula down the column:
=B2*C2
After copying the formula, the worksheet contains six formulas in column D.
Another alternative uses a single formula (an array formula) to calculate all six values in D2:D7.
This single formula occupies six cells and returns an array of six values.
To create a single array formula to perform the calculations, follow these steps:
1. Select a range to hold the results. In this example, the range is D2:D7.
2. Enter the following formula:
=B2:B7*C2:C7
3. Normally, you press Enter to enter a formula. Because this is an array formula, however, you press Ctrl+Shift+Enter.
The formula is entered into all six selected cells. If you examine the Formula bar, you’ll see the following:
{=B2:B7*C2:C7}
Excel places curly brackets around the formula to indicate that it’s an array formula.
This formula performs its calculations and returns a six-item array. The array formula actually works with two other arrays, both of which happen to be stored in ranges. The values for the first array are stored in B2:B7, and the values for the second array are stored in C2:C7.
Because displaying more than one value in a single cell is not possible, six cells are required to display the resulting array. That explains why you selected six cells before you entered the array formula.
This array formula, of course, returns exactly the same values as these six normal formulas entered into individual cells in D2:D7:
=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7
Using a single array formula rather than individual formulas does offer a few advantages:
- It’s a good way of ensuring that all formulas in a range are identical.
- Using a multicell array formula makes it less likely that you will overwrite a formula accidentally. You cannot change one cell in a multicell array formula.
- Using a multicell array formula will almost certainly prevent novices from tampering with your formulas.
No comments:
Post a Comment