Excel Forum
Similar Content
Vlookup to Return All Matching Results
Tutorial: Here is an Excel formula that will act like a Vlookup that returns every matching result ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Tutorial: Lets learn how to put multiple functions and formulas in a single cell in Excel in order t...
Stop Formula Calculation in Excel - Increase Worksheet Performance
Tutorial: I'll show you how to stop formulas and functions from performing their calculations and u...

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.