Excel Forum

Excel 2010 Magic Trick 668: AGGREGATE Function Array Formula #3 Calculate 90 Percentile CPA Scores

See the amazing new AGGREGATE function calculate the 90th percentile CPA score for four different schools. This new function is better than SUBTOTAL function because 1) the options argument set to 18 will calculate Percentile and 2) AGGREGATE can handle arrays. See how the AGGREGATE function can ignore errors.
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.