Using Array Formulae

Excel functions are great when they have to return a single value, SUM(), MAX(), LEFT() all return the results of their processing into the cell the formula is entered in. What happens though when the formula needs to return multiple results?

The Array Formula is designed to handle just such a situation. It allows the results to be spread over a range of cells, so each cell contains one of the results.

The PersisTables Toolkit makes use of Array formulae to unpack the information held in a Table. The Table can hold many values and can be accessed through the Table Handle returned to the cell the formula generating the Table is entered in.

In this simple example the Range of cells B4:C9 is captured in the “powers” Table

The data can be extracted using the TKTable_Get() function.

However on its own we just see the top left value. To extract the full set of data we create an Array Formula by selecting the range we want to extract the data to and then pressing the [F2] key on the top left cell. This displays the TKTable_Get() formula

Now pressing the [CTRL][Shift] and [Enter] keys together creates the Array Formula

The braces in the formula bar indicate the cell highlighted (E6) is part of an Array formula.