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

© 1995-98 Leap of Faith Research Inc.