Microsoft Office Tutorials and References

In Depth Information

**Using Single-Cell Array Formulas**

Figure 14-16:
Without an array formula, calculating the average change requires intermediate formulas in

column D.

How does it work? The formula uses two arrays, the values of which are stored in two ranges

(B2:B15 and C2:C15). The formula creates a
new
array that consists of the differences between

each corresponding element in the other arrays. This new array is stored in Excel’s memory, not in

a range. The AVERAGE function then uses this new array as its argument and returns the result.

The new array consists of the following elements:

{11,15,–6,1,19,2,0,7,15,1,8,23,21,–11}

The formula, therefore, is reduced to the following:

=AVERAGE({11,15,–6,1,19,2,0,7,15,1,8,23,21,–11})

Excel evaluates the function and displays the result, 7.57.

You can use additional array formulas to calculate other measures for the data in this example.

For instance, the following array formula returns the largest change (that is, the greatest

improvement). This formula returns 23, which represents Linda’s test scores:

{=MAX(C2:C15-B2:B15)}

The following array formula returns the smallest change (that is, the least improvement). This

formula returns –11, which represents Nancy’s test scores:

{=MIN(C2:C15–B2:B15)}