The Excel AGGREGATE function returns a aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).
The function number here is 14, which runs the LARGE function. Because the LARGE function requires a k argument, it appears as the last argument in the three formulas above.
The table below lists the function numbers available to the AGGREGATE function, along with the name of the associated function. The third column, Ref2, indicates the second argument value expected when required.
To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 is:
To average a list of values, ignoring any errors that might exist in the range, you can use the AVERAGEIF or AGGREGATE function, as described below. In the example shown, the formula in E5 is:
To get the maximum value in numeric data while ignoring all errors, you can use the AGGREGATE function, as explained below. In the example shown, the formula in E5 is:
where "values" is the...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.