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:

=AVERAGE(D2:D9)

The formula in cell D12 uses the AGGREGATE function, with the option to ignore error values:

=AGGREGATE(1,6,D2:D9)

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.