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
The new array consists of the following elements:
The formula, therefore, is reduced to the following:
Excel evaluates the function and displays the result, 7.57.