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
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:
The formula, therefore, is reduced to the following:
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:
The following array formula returns the smallest change (that is, the least improvement). This
formula returns –11, which represents Nancy’s test scores: