## Using Array Formulas

Many of my User Defined Functions (UDFs) return an array of values, that requires a special procedure to make all the values visible. I have usually given a brief description of the procedure with the UDF, but for convenience for future posts I will give a more detailed discussion here, and link to it in future.

In addition the use of array formulas is a powerful technique for use with built-in functions, so I will start with looking at that aspect.

As a simple example, take the case of generating a multiplication table from a row and column of numbers, each from 1 to 10.  One way is to enter a formula with a mixed absolute/relative address, and copy this over the required range, so with the numbers 1-10 in Row 2 and Column C:

=C\$2*\$B3

when copied from C3 to L12 gives: The same result can be reached using an array formula:

=C14:L14*B15:B24

The procedure is:

• Enter the formula as shown in cell C15 and press enter as usual
• Select the entire range where output values are required: C15:L24
• Press F2 to enter “edit” mode
• Hold down the Ctrl and Shift keys and press Enter to enter the formula as an array formula
• The results will now display over the selected range, and the formula will show in the edit bar surrounded by {}

A similar result is given by the built-in function MMult().  Note that to get the desired result with MMult the order is important.  Specify the row first, then the column:

=MMULT(B15:B24,C14:L14)

The same procedure is used with a UDF that returns an array result.  For instance the procedure for the CSplineA function is:

Important points to be aware of when using an array function are:

• Any change to the function parameters will apply to the entire output range, and must be entered with Ctrl-Shift-Enter (just Enter will generate an error message).
• You can extend the range of an array formula by selecting the extended range (including all of the original range) and pressing F2 followed by Ctrl-Shift-Enter
• You cannot reduce the range of an array formula.  You must delete the entire range, then re-enter over the reduced range.
• You cannot modify the formula over part of the range.
This entry was posted in Arrays, Excel, UDFs and tagged , , . Bookmark the permalink.

### 20 Responses to Using Array Formulas

1. Paulh says:

Nice clear description, thanks. Is there any advantage to using the array formula version of the multiplication (that is the equation =C14:L14*B15:B24)?

Also when I know I’m going to enter an array formula I start by selecting the range for the formula, type in the equation, and then press Ctrl-Shift-Enter. It reduces my confusion as some array formulas will return an error if you just hit enter to begin with.

Like

2. dougaj4 says:

Paul – one advantage of using an array formula rather than copying a formula with a relative address is that if you need to change it you just need to edit one cell, then re-enter as an array, and it automatically updates the whole range. That said, I rarely use arrays in that way, and the example given was just for illustration.

Good point about some array formulas returning an error if you don’t use ctrl-shift-enter for the initial entry.

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.