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

Table using an array formula

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)

Table using MMult

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

- Enter the function at the top of the desired output range: =csplinea($A$34:$A$38,$B$34:$B$38,$C$34:$C$55,1,1,B42,B43)
- Select the complete output range: D34:D55
- Press F2
- Press Ctrl-Shift-Enter; the result (and associated graph) is shown below
- CSplineA Function results

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.

### Like this:

Like Loading...

*Related*

Pingback: Using LinEst() on data with gaps | Newton Excel Bach, not (just) an Excel Blog

Pingback: Using LatPilePY | Newton Excel Bach, not (just) an Excel Blog

Pingback: Section Properties from Coordinates Without VBA | Newton Excel Bach, not (just) an Excel Blog

Pingback: Dots and Crosses | Newton Excel Bach, not (just) an Excel Blog

Pingback: Area from Vectors | Newton Excel Bach, not (just) an Excel Blog

Pingback: Spline Interpolation Alternatives | Newton Excel Bach, not (just) an Excel Blog

Pingback: Using Beam Design Functions | Newton Excel Bach, not (just) an Excel Blog

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.

LikeLike

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.

LikeLike

Pingback: Equivalent Stress Blocks | Newton Excel Bach, not (just) an Excel Blog

Pingback: Linestgap with data in rows | Newton Excel Bach, not (just) an Excel Blog

Pingback: Strand7 API GetNode functions | Newton Excel Bach, not (just) an Excel Blog

Pingback: Extracting selected data with array functions. | Newton Excel Bach, not (just) an Excel Blog

Pingback: Units for Excel | Newton Excel Bach, not (just) an Excel Blog

Pingback: Using RC Design Functions – 1 | Newton Excel Bach, not (just) an Excel Blog

Pingback: Using Index() as an array function | Newton Excel Bach, not (just) an Excel Blog

Pingback: Copy non-blank rows to another sheet | Newton Excel Bach, not (just) an Excel Blog

Pingback: SelectAv Function | Newton Excel Bach, not (just) an Excel Blog

Pingback: EvalA update and examples | Newton Excel Bach, not (just) an Excel Blog

Pingback: Python matrix functions in Excel, using Pyxll | Newton Excel Bach, not (just) an Excel Blog