Microsoft Office Tutorials and References
In Depth Information
Tip 45: Using the AGGREGATE Function
Figure 45-2 shows an example of how the AGGREGATE function can be useful. The worksheet
contains pre-test and post-scores for eight students. Note that Charles did not take the post-test, so cells
C4 and D4 contain the NA/# error value (to indicate not available).
Cell D11 contains a formula that uses the AVERAGE function to calculate the average change. This
formula returns an error:
The formula in cell D12 uses the AGGREGATE function, with the option to ignore error values:
Figure 45-2: Using the AGGREGATE function to calculate an average when the range contains an error value.
Remember that AGGREGATE works only with Excel 2010 and later. If a workbook that uses this
function is opened in a previous version of Excel, the formula will display an error.