Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

Using Excel’s Formula Evaluator

If you would like to better understand how some of these complex array formulas work, consider

using a handy tool: The Formula Evaluator. Select the cell that contains the formula and then

choose Formulas➜Formula Auditing➜Evaluate Formula. You’ll see the Evaluate Formula dialog

box as shown in the figure.

Click the Evaluate button repeatedly to see the intermediate results as the formula is being

calculated. It’s like watching a formula calculate in slow motion.

If two values in the
Data
range are equidistant from the
Target
value, the formula returns the first

one in the list. Figure 15-7 shows an example of this formula. In this case, the
Target
value is 45.

The array formula in cell D4 returns 48 — the value closest to 45.

Figure 15-7:
An array formula returns the closest match.