AGGREGATE(function_num, options, ref1, [ref2], ...)
=AGGREGATE(4,7,A1:A10)
returns the maximum value in the range A1:A10. This example finds the largest value in the range A1 to A10.
=AGGREGATE(5,7,A1:A10)
returns the minimum value in the range A1:A10. This example finds the smallest value in the range A1 to A10.
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)
returns the largest value on Mondays and ignores errors. This example uses the AGGREGATE function to find the largest value on Mondays, while ignoring any errors that might appear.
=AGGREGATE(14,6,values,1)
returns the first largest number in the values. This example finds the largest value in the values provided.
=AGGREGATE(14,6,values,2)
returns the second largest number in the values. This example finds the second largest value in the values provided.
=AGGREGATE(14,6,values,3)
< returns the third largest number in the values. This example finds the third largest value in the values provided.
The AGGREGATE function can be used to perform aggregations in a list or database and it can ignore hidden rows and error values. It is meant for vertical ranges, but not for horizontal ranges.