##### Using Formula Evaluation

If you’re using Excel 2002 or a more recent version,
you have access to a formula evaluation tool. Begin by selecting a cell
that contains a formula. Then start formula evaluation. In Excel 2007
and 2010, you’ll find it on the Ribbon’s Formulas tab, in the Formula
Auditing group; in Excel 2002 and 2003, choose Tools, Formula Auditing,
Evaluate Formula. If you were to begin by selecting a cell with the
array formula that this section has discussed, you would see the window
shown in Figure 5.

Now, if you click Evaluate, Excel begins evaluating the formula from the inside out and the display changes to what you see in Figure 6.

Click Evaluate again and you’ll see the results of the test of A2:A21 with C1, as shown in Figure 7.

Click Evaluate again and the window shows the
results of the IF() function, which in this case replaces TRUE with 1
and FALSE with 0 (see Figure 8).

A final click of Evaluate shows you the final
result, when the SUM() function totals the 1’s and 0’s to return a
count of the number of instances of Ford in A2:A21, as shown in Figure 9.

You could use the SUMIF() or COUNTIF() function if
you prefer. I like the SUM(IF()) structure because I find that it gives
me more flexibility in complicated situations such as summing the
results of multiplying two or more conditional arrays.

##### Using the Recalculate Key

Another method for looking inside a formula is
available in all Windows versions of Excel, and makes use of the F9
key. The F9 key forces a calculation and can be used to recalculate a
worksheet’s formulas when automatic recalculation has been turned off.

If that were all you could do with the F9 key, its
scope would be pretty limited. But you can also use it to calculate a
portion of a formula. Suppose you have this array formula in a
worksheet cell and its arguments as given in Figure 2:

=SUM(IF(A2:A21=C1,1,0))

If the cell that contains the formula is active,
you’ll see the formula in the Formula box. Drag across the A2:A21=C1
portion with your mouse pointer to highlight it. Then, while it’s still
highlighted, press F9 to get the result shown in Figure 10.

Note

Excel formulas separate rows by semicolons and columns by commas. The array in Figure 10
is based on values that are found in different rows, so the TRUE and
FALSE items are separated by semicolons. If the original values were in
different columns, the TRUE and FALSE items would be separated by
commas.

If you’re using Excel 2002 or later, use
formula evaluation to step through a formula from the inside out.
Alternatively, using any Windows version, use the F9 key to get a quick
look at how Excel evaluates a single fragment from the formula.