Microsoft Office Tutorials and References

In Depth Information

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.

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

column D.

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.