How To Use Array Formulas

How To Use Array Formulas

Some functions return arrays as their results (e.g. the MBS_Tabular function returns a 1 by 12 array) in order to maximize speed.

It is often tricky to enter Array Formulas, as the standard approach discussed in the Excel On-line Help avoids use of the Function Wizard. To enter the forumulas using the Function Wizard, we suggest you do the following:

1. Select a cell on the spreadsheet, and enter the function and its inputs using the Function Wizard. When you click on "Finish", Excel will copy the formula to the cell, and the function will return only the first cell of the array (e.g. the price result for the MBS_Tabular function, and not the rest of the results).

2. Next, select all of the text of the formula (except the leading "=" character) from the formula bar of the cell (the formula bar is near the top of the spreadsheet, below the toolbars). The text can be selected using the mouse. One can copy the text of this formula into the clipboard by simply typing CTRL-C (Control and the "C" keys at the same time).

3. Next, select (highlight) the cells which will hold the result of the array function (in the case of the MBS_Tabular function, this would be a 1 by 12 array of cells). While all the cells are selected, type the "=" key (i.e. the "equals" key), and then CTRL-V (Control and the "V" keys at the same time) to copy the formula from step 2 into the formula bar.

4. Hit CTRL-SHIFT-ENTER (i.e. Control, Shift, and Enter keys, simultaneously) to finish entering the Array Formula. The array formula will be evident because curly brackets will surround the regular formula. In the case of the MBS_Tabular function it should now return 12 values.

5. Delete the formula from the temporary cell in step 1.

As you can see, it's a bit tricky to learn at first, but array formulas are very useful and powerful, as they allow us to return multiple results from just a single function call, saving much computation time (e.g. in the case of the MBS_Tabular function, the additional statistics beyond "price" are very simple to calculate while calculating the price; if we were to calculate them separately, it would take 12 times longer, compared to calculating them all at once).