Newton Excel Bach, not (just) an Excel Blog

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.