Understanding Array Formulas and Functions in Excel

In This Chapter

Understanding arrays
Creating formulas that use arrays
Using functions that return arrays of data
Excel is really quite sophisticated — its many built-in functions make your work easier. On top of that, Excel allows you to tell functions to work on entire sets of values, called arrays, which makes for even more clever analysis.
An array is a set of two or more values (for example, the contents of two or more worksheet cells, or even the contents of two or more worksheet ranges). Certain functions use arrays for arguments.
You may be thinking, “Hey, how is this different than just entering a bunch of arguments?” You’re right in the comparison. For example, the SUM function can take up to 30 arguments. Isn’t this the same as giving the function an array with 30 values? Well, yes and no. It’s the same idea, but the way you apply the array can produce different results, as you will soon see.
There is even another side to array functions. Some of the functions return an array. Yes, that’s right. Most of the time a function returns a single value into a single cell. In this chapter, we show you how some functions return a group of values into multiple cells.

Discovering Arrays

An array is like a box. It can hold a number of items. In Excel, an array holds a collection of values or cell references. These arrays are used exclusively in formulas and functions. That is, the association of some values as one cohesive group exists just for the purpose of calculating results. An array differs from the named areas (a range of cells) that you can create in Excel. Named areas become part of the worksheet and can referenced at anytime.
Named areas are set using the Define Name dialog box, shown in Figure 3-1. In contrast, there is no such dialog box or method to create arrays that can be referenced from functions or formulas. Arrays, instead, are embedded in formulas.

Figure 3-1:
Creating a named area with the Define Name dialog box.
Named areas are easily referenced in formulas. For example if a workbook contains a named area “Sales,” then the values of all the cells in “Sales” can be summed up like this:

Let’s assume that “Sales” contains three cells with these values — 10, 15, and 20. These values of course can be directly entered in the SUM function like this:

This is almost an array, but not quite. Excel recognizes a group of values to be an array when they are enclosed with braces ({ and }). Therefore to enter the array of values into the function, the entry looks like this:

Essentially the braces tell Excel to treat the group of values as an array. So far, you may be wondering about the usefulness of an array, but in the next section, we show you how using arrays with standard functions such as SUM can provide sophisticated results.
To enter values as an array within a function, enclose them with braces.

Using Arrays in Formulas

You can use arrays when entering formulas and functions. Typically the arguments to a function are entered in a different manner, which we demonstrate in this section. Using arrays can save entry steps and deliver an answer in a single formula. This is useful in situations that normally require a set of intermediate calculations from which the final result is calculated .We don’t know about you but we like shortcuts, especially when we have too much to do!
Here’s an example. The SUM function is normally used to add a few numbers together. Summing up a few numbers doesn’t require an array formula per se, but what about summing up the results of other calculations? This next example shows how using an array simplifies getting to the final result.
Figure 3-2 shows a small portfolio of stocks. Column A has the stock symbols, Column B has the number of shares per security, and Column C has recent prices for each.

Figure 3-2:
A stock portfolio.
The task is to find out the total value of the portfolio. The typical way to do this is to:
1. Multiply the number of shares for each stock by its price.
2. Sum up the results from Step 1.
Figure 3-3 shows a very common way to do this. Column D contains formulas to calculate the value of each stock in the portfolio. This is done by multiplying the number of shares for each stock by its price. For example, cell D4 contains the formula =B4*C4. Cell D10 sums up the interim results with the formula =SUM(D4:D8).

Figure 3-3:
Calculating the value of a stock portfolio, the old-fashioned way.
The method shown in Figure 3-3 requires creating additional calculations — those in Column D. These are necessary if you need to know the value of each stock, but not if all you need to know is the value of the portfolio as a whole.
Fortunately, alternatives to this old-fashioned approach exist. One is to embed the separate multiplicative steps directly inside the SUM function, like this:

That works, but it’s bloated to say the least. What if you had 20 stocks in the portfolio? Forget it!
Another alternative is the SUMPRODUCT function. This function sums the products, just as the other methods shown here also do. The limitation, however, is that SUMPRODUCT can only be used for summing. It cannot, for example, give you an average.
In many situations such as this one, your best bet is to use an array function. Figure 3-4 shows the correct result from using the SUM function entered as an array function.

Figure 3-4:
Calculating the value of a stock portfolio using an array function.
The syntax is important. Two ranges are entered into the function: One contains the cells that hold the number of shares, and the other contains the cells that have the stock prices. These are multiplied in the function by entering the multiplication operator (*):

Of course Ctrl+Shift+Enter had been pressed to turn the whole thing into an array function.
Use Ctrl+Shift+Enter to turn a formula into an array formula. You must use the key combination after entering the formula but before pressing the Enter key. The key combination takes the place of the standard Enter key press.

Try this out. Here’s how you use an array with the SUM function:

1. Enter two columns of values.
The two lists must be the same size.
2. Position the cursor in the cell where you want the result to appear.
3. Enter =SUM( to start the function.
4. Click on the first cell in the first list and drag the mouse over the first list. Then release the mouse button.
5. Enter the multiplication sign (*).
6. Click on the first cell in the second list and drag the mouse over the second list. Then release the mouse button.
7. Enter a closing parenthesis.
8. Press Ctrl+Shift+Enter to end the function.
Array functions are useful for saving steps in mathematical operations. Therefore you can apply these examples to a number of functions, such as AVERAGE, MAX, MIN, and so on.
As another example, suppose you run a fleet of taxis and you need to calculate the average cost of gasoline per mile driven. This is easy to calculate for a single vehicle. You just divide the total miles driven by the total spent on gasoline, for a given period of time. The calculation looks like this:
cost of gasoline per mile = amount spent on gasoline miles driven
How can you easily calculate this for a fleet of vehicles? Figure 3-5 shows how this is done. The vehicles are listed in Column A, the total miles driven for the month appear in Column B, and the total amounts spent on gasoline appear in Column C.

Figure 3-5:
Making an easy calculation using an array formula.
One single formula in cell C21 answers the question. By using the AVERAGE function in an array formula, the result is returned without the need for any intermediate calculations. The formula looks like this:

Working with Functions That Return Arrays

A few functions actually return arrays of data. Instead of providing a single result, as most functions do, these functions return several values. The number of actual returned values is directly related to the function’s arguments. The returned values go into a range of cells.
Excel array functions are special functions that accept arrays as arguments and possibly return arrays of data.
A good example of this is the TRANSPOSE function. This interesting function is used to reorient data. Data situated a given way in columns and rows is transposed (changed to be presented instead in rows and columns). Figure 3-6 shows how this works.

Figure 3-6:
Transposing data.
Cells B3 through D10 contain information about departments in a company. Departments are listed going down Column B. Note that the area of B3 through D10 specifically occupies three columns and eight rows. The header row is included in the area.
Cells B16 through I18 contain the transposed data. It is the same data, but now it occupies eight columns and three rows. In total number of cells, this is the same size as the original area. Just as important is that the area is made up of the same dimensions, just reversed. That is, a 3-by-8 area became an 8-by-3 area. The number of cells remains at 24. However the transposed area has not been altered to be 6 by 3 or 2 by 12, or any other two dimensions that cover 24 cells.
Every single cell in the B16:I18 range contains the same formula — {=TRANSPOSE(B3:D10)} — however the function was entered only once.
In detail, here is how you can use the TRANSPOSE function:
1. Enter some data that occupies at least two adjacent cells at a minimum.
Creating an area of data that spans multiple rows and columns is best for seeing the how useful the function is.
2. Elsewhere on the worksheet, select an area that covers the same number of cells, but has the length of the sides of the original area reversed.
For example:
• If the original area is two columns and six rows then select an area that is six columns and two rows.
• If the original area is one column and two rows then select an area that is two columns and one row.
• If the original area is 200 columns and 201 rows then select an area that is 201 columns and 200 rows.
• If the original area is five columns and five rows then select an area that is five columns and five rows. (A square area is transposed into a square area.)
Figure 3-7 shows an area of data and a selected area ready to receive the transposed data. The original data area occupies 11 columns and 3 rows. The selected area is 3 columns by 11 rows.

Figure 3-7:
Preparing an area to receive transposed data.
3. Enter =TRANSPOSE( to start the function.
4. Click on the first cell in the original data and drag the mouse over the entire original data area while keeping the mouse button down.
The function now shows the range of the area. Figure 3-8 shows how the entry should appear at this step.
5. Enter a closing parenthesis.
6. Press Ctrl+Shift+Enter to end the function.
Note: The transposed data does not take on the formatting of the original area. You need to format the area as needed. Figure 3-9 shows the result of using TRANSPOSE and then formatting the transposed data.

Figure 3-8:
Completing the function.

Figure 3-9:
Transposed data after formatting.
Wait! Isn’t this a waste of time? Excel can easily transpose data using the Paste Special dialog box. Simply copying a range of data and using this dialog box to paste the data gives the same result as the TRANSPOSE function. Or does it?
Figure 3-10 shows the Paste Special dialog box with the Transpose check box checked. This will transpose the data. You don’t even have to select the area first!
However, when data is transposed with the Paste Special dialog box, the actual data is copied to the new area. By contrast the TRANSPOSE function pastes a formula that references the original data — and that is the key point.
When data is changed in the original area, the change is reflected in the new, transposed area, if the TRANSPOSE function was used.
You can transpose data in two ways. The TRANSPOSE function references the original data and will update as original data is changed. Using the Paste Special dialog box to transpose data creates values that do not update when the original data changes.

Figure 3-10:
Using the Paste Special dialog box to transpose data.