Principles of Financial Modelling

CHAPTER 18Array Functions and Formulae

INTRODUCTION

This chapter presents a general discussion of array functions and formulae. It is worth treating these before presenting many other Excel functions: not only are array functions present in several Excel function categories, but also essentially any Excel function can be used as part of an array formula. The chapter covers the core principles, which are used at various places elsewhere in the text.

Functions and Formulae: Definitions

The essential feature of array functions and formulae is that “behind the scenes” they perform calculations that would otherwise be required to be conducted in several ranges or multi-cell tables. The output (return statement) of an array function generally extends over a range of multiple contiguous cells; however, some return values only to a single cell.

The distinction between functions and formulae is:

• Array functions are in-built Excel functions which inherently require the use of a contiguous array in their calculations or output form. They are a type of function, not a separate function category. Examples include TRANSPOSE (Lookup and Reference category), MMULT, MINVERSE (Math&Trig category) and FREQUENCY and LINEST (Statistical category). Some user-defined functions that are created using VBA macros may also be written to be array functions.
• Array formulae use standard Excel functions or operations (such as SUM, MIN or IF), but are written so that intermediate calculation steps are ...