Excel Forum

Excel 2010 Magic Trick 667: AGGREGATE Function Array Formula #2 Return 5 Largest Values

See how to calculate the five largest values for each sales representative using the new AGGREGATE function. In the AGGREGATE, see the large function for the first argument and 6 (ignore errors) in the options argument (second argument of the AGGREGATE). This new function can handle arrays and will create a whole table of values. Compare it to earlier Excel methods where we had to use the IF and LARGE functions and CTRL + SHIFT + ENTER.
AGGREGATE function joins the ranks of LOOKUP, INDEX and SUMPRODUCT as functions that can handle arrys!
Array formulas: 1) For AGGREGATE function, Numbers come first, then divide, then criteria; 2) For IF function, Criteria come first, then comma, then numbers.

Download files: http://flightline.highline.edu/mgirvin/ExcelIsFun.htm

Got a Question? Ask it Here in the Forum.