##### Array Function
How to use this most powerful and efficient Excel feature in Business Functions
What is an Array Function?

An array function is literally a function that returns an array of values, rather than a single value. As such an array function needs to pertain to a range, not a single cell. If you put an array function in a single cell, you will just get the first value of the array returned. To apply a function to an array of cells, you press Control-Shift-Enter whilst the array range is highlighted (see Excel documentation for more details).

Excel"s use of array functions can be confusing. Whilst there are some functions that actually require a range to put there answers into, there are others that can either be used on a single cell or array basis.

How does Business Functions use the Array Function Concept?

There are two classes of array function in BF.
• Functions that can only be used as array functions such as PolyReg, which returns a range of coefficients. This category is very simple. The function returns a range so you must provide a range, by way of applying the formula as an array function (Ctrl-Shift-Enter).
• Functions which can optionally be used as array functions, such as FStep or any of the projections functions. These functions, in normal use, return a value for a single time period as specified by Time and Base. Array functions enable you to apply the function to a range of time periods all in one go - and yet still using the same function. All you do is provide a range of Times note that Base is ignored, the timeperiod length being determined from the Times If you then apply this to an array, the first cell contains the result for the first time period, the second cell the second result, etc. For functions that do a lot of detailed calculations that refer across time periods, this approach can save a lot of calculation time. One function that yields dramatic improvements in this manner is LoanX.

What are the array function tools?

Most people find Excels Ctrl-Shift-Enter methodology rather cumbersome and minimalist. So we developed a couple of useful menu options.

• Expand expands a formula, array or normal, from the leftmost or topmost cell in a selected range to the rest of that range, creating an array formula.
• Collapse eliminates the array formula from the selected range, placing a single formula in the top left most cell.

Remember also the primary method of entering array formulas that Excel provides is to go Ctrl-Shift-Enter in the formula pane, and the formula will be "arrayed" into the currently selected range.

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure