|
|
Featured Tutorial
Featured Macro
Excel 2010 Magic Trick 666: AGGREGATE Function Array Formula #1 Single Cell Array Formula
See how to use the AGGREGATE function to avoid Ctrl + Shift + Enter for normal array formulas. We will compare the new Excel 2010 AGGREGATE function to older methods of calculating the 2nd largest value in a data set with 1 criteria (condition). See the IF and LARGE 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).
AGGREGATE function joins the ranks of LOOKUP, INDEX and SUMPRODUCT as functions that can handle arrays!
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
AGGREGATE function joins the ranks of LOOKUP, INDEX and SUMPRODUCT as functions that can handle arrays!
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