Part II: Working with Formulas and Functions
FIGURE 18.17
Without an array formula, calculating the average change requires intermediate formulas
in column D.
With an array formula (see Figure 18.7), 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, calculated from the two ranges, 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 this:
=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.
