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.