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

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.
This entry was posted in Arrays, Excel, UDFs and tagged , , . Bookmark the permalink.

20 Responses to Using Array Formulas

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

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

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

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

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

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

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

  8. 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

  9. 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

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

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

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

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

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

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

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

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

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

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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s