Introducing Array Formulas, an Exotic Beast
One of the most obscure things about Excel has to be array formulas. Weird as snake’s suspenders, they are.
We are familiar with the idea that a single formula in Excel occupies a single cell. If you want that formula to be in many cells, you simply copy it to those cells. It will adjust to the new position.
An array formula, on the other hand, applies to a range of cells as a unit. You enter this formula in a very special way. Instead of the usual Enter, Tab, Shift+Enter, Shift+Tab, or Arrow Key to enter it, press three keys at the same time: Shift+Ctrl+Enter.
Formulas that are part of an array formula appear in braces {curly brackets}. The braces disappear while you are editing the formula. Woe betide you if you actually type the braces!
Before starting to type an array formula, you must select all the cells that will form the array. Then type the formula and hit Shift+Ctrl+Enter. It is easy to extend that range later, more difficult to reduce it.
Array Formulas Example: TRANSPOSE
Let’s do an example with possibly the simplest array formula, TRANSPOSE.
We all know about Copy – Paste Transpose (or, at least, we should!). The drawback with that is that the pasted result is not linked to the original figures. If the original figures change, the pasted result stays exactly as it was. By contrast, the TRANSPOSE formula, being a formula linked to the source, will update if the original figures change.
Enter different figures into a spreadsheet, in a range (say) 2 columns wide and 3 rows high. Then, in a different area of the spreadsheet, select a range 3 columns wide and 2 rows high. That is because the rows will transpose to columns and the columns will transpose to rows. Select your new range. Type =TR and press Tab to complete =TRANSPOSE( then select the range containing your original figures. Hold in Shift and Control and, while doing so, press Enter. The whole selected range fills with the formula {=TRANSPOSE(range)} (note the braces). That range should show the transposition of your original range.
Now change some of the figures in your original range. You should see the corresponding figures change in the transposed Range as well.
Editing an Array Formula
You can change an array formula in any cell of the array, but you must select the whole array first.
Press F2 (Edit) or click in the Edit Box.
Make your changes.
Press (at the same time) Shift+Ctrl+Enter.
Expanding an Array Formula
Expanding the range for an Array Formula is easy:
In the case of the above example, add a row of two new figures under the existing source data. Of course, they do not feature in the current TRANSPOSE formula, which expects data 3 rows high.
Select the extended range for the TRANSPOSE formula. Start on a cell that contains the formula. That is, if you are extending to the right, then start selecting on the left of the existing range.
Press F2 (Edit) or click in the Edit Box.
Press (at the same time) Shift+Ctrl+Enter.
Reducing the Range of an Array Formula
Contracting the range for an Array Formula is more complicated.
If you reduce the original range, it does not reduce the range occupied by the Array Formula. For example, in the example above, try deleting row 6. Note: Delete the whole row, or at least cells A6:B6, not just the cell contents. What happened? The formula adjusted to =TRANSPOSE(A3:B5), but the array formula still occupies the range D4:F5. The corresponding cells in the formula, G4 and G5, sport the #N/A error.
You will get the same effect if you enter any array formula with more than the number of rows or columns it requires.
OK, let’s try using the method we used to expand the range. Select only the range we need for the revised TRANSPOSE formula, in our case D4:F5. Press F2 (Edit) or click in the Edit Box. Press (at the same time) Shift+Ctrl+Enter.
Instead of reducing the range, Excel gives the error: “You can’t change part of an array”:
You get the same error if you select column G and hit Ctrl- (minus) to delete that column.
What you have to do is start afresh (almost).
Select the whole range of the array formula, D4:G5. Press F2 (Edit) or click in the Edit Box. Select the formula. Copy it to the clipboard, for example with the Ctrl+C shortcut key. Press Escape to exit Edit mode. Now press the Delete key to clear the entire range.
Note that you have to do the copy in Edit Mode. If you do it from Ready Mode, Excel loses your copy selection (the box with the green broken line) as soon as you press the Delete key.
Now shrink your selection to encompass only the reduced range for the array formula, D4:F5. Press F2 (Edit) or click in the Edit Box. Paste from the clipboard, for example with the Ctrl+V shortcut key. The formula =TRANSPOSE(A3:B5) should appear. Enter it as an array formula using our friend, Shift+Ctrl+Enter as before.
Deleting an Array Formula
This at least is easy, but you must select the whole array first.
Select the array (all cells). Shortcut: click on any cell in the array and press Ctrl+/.
Now press the Delete key to clear the entire range.
Job done!
Array Formulas Examples: COLUMN, SUM
We are used to using the =COLUMN(cell_ref) formula to give us the column number of a cell.
Less obvious is the fact that we can enter that same formula as an array formula using a multi-cell range reference. This gives an array of answers. Thus, select (say) cells A5:C5, type =COLUMN(A3:C3), and enter it as an array formula with Shift+Ctrl+Enter.
You get the numbers 1, 2, 3 in cells A5, B5 and C5, being the column numbers of cells A3, B3 and C3 respectively.
One doesn’t in fact have to use cell addresses: Column letters will do. Thus: =COLUMN(B:D) giving 2, 3, 4 in (say) A7:C7 (although =COLUMN(B), a single column on its own, doesn’t work).
How can one use this?
For example, in a sum: just as one could =SUM(A7:C7) above, once can also =SUM(COLUMN(B:D)). The quirk here is that, even entering it into one cell, you have to enter it with Shift+Ctrl+Enter as an array formula. If you enter it as a regular formula with plain Enter, the result is 2, summing the first column only!
Array Formulas Example: LINEST
The LINEST function is best known for fitting a straight line equation to data. The syntax is LINEST(known_y’s, [known_x’s], [const], [stats]). We’ll use only the first two arguments in this article.
The straight line equation is y=bx+c
You always enter LINEST as an array formula. For a straight line, first select two adjacent cells on the same row. These cells will contain the constants b and c in the formula. They are respectively the slope and y-intercept of the straight line.
The example below comes from Microsoft Help. We have known y values of {1, 5, 7, 9} in cells A4:A7 and known x values of {0, 2, 3, 4} in cells B4:B7. Select cells A10:B10 for the results. Type =LINEST(A4:A7, B4:B7, , FALSE) and enter it as an array formula with Shift+Ctrl+Enter.
This produces the results {2, 1} in cells A10:B10. Our equation y=bx+c is thus y=2x+1. We can enter it in cells C4:C7 and plot the results to check:
This is only the beginning. The “known_x’s” argument can consist of more than one column. One way to do this is to raise the original range to several powers, e.g. A20:A24^{1,2,3} in the example below. This is another weird use of an array, this time {1,2,3}, again in braces.
One could use the formula COLUMN(A:C) instead of {1,2,3}. As we saw in the section above dealing with the COLUMN formula, it’s just an obscure way to produce the array {1,2,3}.
Array Formulas Example: LINEST raised to an Array
Here we have known x values in cells A20:A24 and known y values in cells B20:B24. Note that we have swapped x and y compared to the example above. Select cells A26:C26 for the results. Type =LINEST(B20:B24, A20:A24) and enter it as an array formula with Shift+Ctrl+Enter.
The result in the linear equation y=bx+c is y=12x – 7, which we can enter into cells C20:C24 for plotting. As expected, it’s not a good fit. Note the #N/A error in C26 because we selected too many cells.
To fit a Quadratic equation (3 constants), select cells A28:C28. Enter =LINEST(B20:B24, A20:A24^{1,2}) as an array formula.
To fit a Cubic equation (4 constants), select cells A30:D30 (four columns). Enter =LINEST(B20:B24, A20:A24^{1,2.3}) as an array formula.
That is enough mind-stretching for one post! We’ll look at other uses for array formulas in the next post.