# Array Formulas and Functions

The worksheet formulas that we described in Excel Spreadsheets return a single value that is assigned to the cell that contains the formula. Excel also allows you to define a formula that assigns values to a range of cells at the same time. These are called array formulas and they will be used quite often throughout this website.

### Array formulas

We now show how to create formulas that modify multiple cells at the same time.

Example 1: Calculate the revenues for each item in the worksheet of Figure 1.

Figure 1 – Array formula

Given that the revenue for each item is unit price times quantity, we can enter the formula =B4*C4 in cell D4 and then copy this formula into cells D5, D6 and D7 (e.g. by clicking on cell D4, pressing Ctrl-C, then highlighting the range D5:D7 and pressing Ctrl-V, or by highlighting the range D4:D7 and pressing Ctrl-D).

Another way to do this is via an array formula, using the following steps:

1. Highlight the range D4:D7
2. Enter the array formula =B4:B7*C4:C7
3. Press Ctrl-Shft-Enter (i.e. hold down the Ctrl and Shift keys and press the Enter key)

It is essential to press Ctrl-Shft-Enter (step 3) and not simply Enter as for an ordinary formula. Note that the formula that appears in the formula bar is {=B4:B7*C4:C7}. The curly brackets indicate that this is an array formula. If the range B4:B7 is given the name UnitPrice and C4:C7 is given the name Quantity, then the array formula can be entered as =UnitPrice*Quantity (step 2);

The array formula appears in all four cells in the range D4:D7. To make changes to the formula you must edit the entire range and not just one, two or three of these cells. Similarly you can’t copy or delete a part of the range, but must copy or delete the entire range. If you attempt to modify a part of the range you will receive an error message. If you get stuck and get a series of such error messages you just need to press the escape key Esc to recover.

You can erase a range that contains an array formula by highlighting the entire range and pressing the Delete key. You can write over the array function replacing it by a value or another formula. The important thing is to use the entire range and not a part of the range.

You can also extend the range covered by an array formula by clicking on any one of the four corners of the range and then highlighting a range which includes the original range. Finally you click anywhere on the formula bar and press Ctrl-Shft-Enter.

Note too that you can also use array formulas such as {=SUM(B4:B7*C4:C7)}. This returns the value which is the sum of the revenues of the four types of equipment. Even though this formula returns a single value, and so may be placed in a single cell such as D8, it must be entered as an array formula (since the formula contains an embedded array formula). This means that you need to type =SUM(B4:B7*C4:C7) and then press Ctrl-Shft-Enter. If you forget to press Ctrl-Shft-Enter and only press Enter, you will get an error message.

### Array functions

A few of Excel’s built-in functions are array functions, where the output of the function is an array. These functions are managed as described above for array formulas.

Example 2: Change the data range in columns A and B of Figure 2 into an equivalent row range.

Figure 2 – Array function

This can be accomplished by means of Excel’s TRANSPOSE array function using the following steps:

1. Highlight the output range D3:I4
2. Enter the array formula =TRANSPOSE(A3:B8)
3. Press Ctrl-Shft-Enter

Note that the output range (step 1) must be of the right size. In this case, the input range is 6 rows by 2 columns, and so the output range must be 2 rows by 6 columns. As for array formulas the formula bar contains the array formula enclosed in curly brackets. Once again it is important to press Ctrl-Shft-Enter.

### 3 Responses to Array Formulas and Functions

1. Iain Peters says:

Dear Dr. Zaiontz,

Your website and the Real Statistics add-in for Excel are AMAZING!!! Thank you so much.

Iain Peters, PhD