Excel 2010 Bible

Chapter 16: Introducing Array Formulas

375

post-test scores. Cell D17 contains a formula, shown here, that calculates the average of the values

in column D:

=AVERAGE(D2:D15)

With an array formula, you can eliminate column D. The following array formula calculates the

average of the changes but does not require the formulas in column D:

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

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 equivalent to

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

Excel evaluates the function and displays the results, 7.57.

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

example, 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)}

FIGURE 16.17

Without an array formula, calculating the average change requires intermediate formulas in column D.