Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

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 anyone 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 simply 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 calculate 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

Figure 14-1: Column D contains formulas to calculate the total for each product.

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 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.

A Single-Cell Array Formula

Now it's time to take a look at a single-cell array formula. Refer again to Figure 14-1. The following array formula occupies a single cell:

{=SUM(B2:B7*C2:C7)}

You can enter this formula into any cell. Remember: When you enter this formula, make sure you press Ctrl+Shift+Enter (and don't type the curly brackets).

This array formula returns the sum of the total product sales. It's important to understand that this formula does not rely on the information in column D. In fact, you can delete column D, and the formula will still work.

This formula works with two arrays, both of which are stored in cells. The first array is stored in B2:B7, and the second array is stored in C2:C7. The formula multiplies the corresponding values in these two arrays and creates a new array (which exists only in memory). The SUM function then operates on this new array and returns the sum of its values.

Creating an Array Constant

The examples in the previous section used arrays stored in worksheet ranges. The examples in this section demonstrate an important concept: An array does not have to be stored in a range of cells. This type of array, which is stored in memory, is referred to as an array constant.

You create an array constant by listing its items and surrounding them with curly brackets. Here's an example of a five-item vertical array constant:

{1,0,1,0,1}

The following formula uses the SUM function, with the preceding array constant as its argument. The formula returns the sum of the values in the array (which is 3). Notice that this formula uses an array, but it is not an array formula. Therefore, you do not use Ctrl+Shift+Enter to enter the formula.

=SUM({1,0,1,0,1})

At this point, you probably don't see any advantage to using an array constant. The formula that follows, for example, returns the same result as the previous formula:

=SUM(1,0,1,0,1)

Keep reading, and the advantages will become apparent.

Following is a formula that uses two array constants:

=SUM({1,2,3,4}*{5,6,7,8})

This formula creates a new array (in memory) that consists of the product of the corresponding elements in the two arrays. The new array is as follows:

{5,12,21,32}

This new array is then used as an argument for the SUM function, which returns the result (70). The formula is equivalent to the following formula, which doesn't use arrays:

=SUM(1*5,2*6,3*7,4*8)

A formula can work with both an array constant and an array stored in a range. The following formula, for example, returns the sum of the values in A1:D1, each multiplied by the corresponding element in the array constant:

=SUM((A1:D1*{1,2,3,4}))

This formula is equivalent to

=SUM(A1*1,B1*2,C1*3,D1*4)

Array Constant Elements

An array constant can contain numbers, text, logical values (TRUE or FALSE), and even error values such as #N/A. Numbers can be in integer, decimal, or scientific format. You must enclose text in double quotation marks (for example, "Tuesday"). You can use different types of values in the same array constant, as in this example:

{1,2,3,TRUE,FALSE,TRUE,"Moe","Larry","Curly"}

An array constant cannot contain formulas, functions, or other arrays. Numeric values cannot contain dollar signs, commas, parentheses, or percent signs. For example, the following is an invalid array constant:

{SQRT(32),$56.32,12.5%}