performed on one or more references.
The second argument,options, controls how AGGREGATE handles errors and values in hidden rows.
See thetable belowfor all available options.
The AGGREGATE function takes fourarguments:function_num,options,ref1, andref2.
For example, functions likeSMALLandLARGEtake a second argument,k, andref2represents that argument.
The function number is 4, which specifies MAX.Optionsis provided as 6, to ignore errors only.
Because the LARGE function requires akargument, it appears as the last argument in the three formulas above.
Then we build alogical expressionusing the TEXT function to check all dates for Mondays.
The result of this operation is anarrayof TRUE/FALSE values, which become the denominator of the original values.
FALSE evaluates as zero, and throws a #DIV/0!
TRUE evaluates as 1 and returns the original value.
The final array of values and errors acts like a filter.
AGGREGATE ignores all errors and returns the largest (maximum) of the surviving values.More complete example here.
The third column,Ref2, indicates the “second argument” expected by the last 6 functions.
Options are set with theoptionsargument.
Possible values are 0-7, as shown in the table below.
However, one subtle difference between AGGREGATE and SUBTOTAL is that the default behavior for hidden rows is reversed.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
MAX Function
The Excel MAX function returns the largest numeric value in the data provided.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
from a set of numeric data.
AVERAGE Function
The Excel AVERAGE function calculates the average (arithmetic mean) of supplied numbers.
COUNT Function
The Excel COUNT function returns a count of values that are numbers.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
