“Data scientists spend 80% of their time cleaning and manipulating data and only 20% of their time actually analyzing it.” To perform the data analytics properly we need various data cleaning techniques so that our data is ready for analysis. Regarding data, there are many things to go wrong – be it the construction, arrangement, formatting, spellings, duplication, extra spaces, and so on. The AGGREGATE function is designed for vertical ranges, not horizontal ranges.Data forms the backbone of any data analytics you do.3D references do not work with AGGREGATE.AGGREGATE returns a #VALUE! error if a second function argument is required, but not provided.Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions Ignore error values, SUBTOTAL and AGGREGATE functions Ignore hidden rows, SUBTOTAL and AGGREGATE functions Possible values are 0-7, as shown in the table below. Options are set with the options argument. The AGGREGATE function has many options for ignoring errors, hidden rows, and other functions. The table below lists the function numbers available to the AGGREGATE function, along with the name of the associated function. The third column, Ref2, indicates the "second argument" expected by the last 6 functions. AGGREGATE ignores all errors and returns the largest (maximum) of the surviving values. The final array of values and errors acts like a filter. TRUE evaluates as 1 and returns the original value. FALSE evaluates as zero, and throws a #DIV/0! error. The result of this operation is an array of TRUE/FALSE values, which become the denominator of the original values. Then we build a logical expression using the TEXT function to check all dates for Mondays. Here we specify 14 for function (LARGE) and 6 for option (ignore errors). = AGGREGATE ( 14, 6 ,values / ( TEXT (dates, "ddd" ) = "Mon" ), 1 ) The following six functions require the ref2 argument: For example, functions like SMALL and LARGE take a second argument, k, and ref2 represents that argument. The last 6 functions require all four arguments: function_num specifies the operation, options sets various behaviors, ref1 is the array of values to process, and ref2 is the "second argument" for the function being called. For the first 13 functions supported, only the first three arguments are required: function_num specifies the operation, options sets various behaviors, and ref1 is the array of values to process. The AGGREGATE function takes four arguments: function_num, options, ref1, and ref2. See the table below for all available options. The second argument, options, controls how AGGREGATE handles errors and values in hidden rows. AGGREGATE can handle many array operations natively, without Control + Shift + Enter.ĪGGREGATE can run a total of 19 functions, and the function to perform is given as a number, which appears as the first argument in the function, function_num.AGGREGATE has a number of options for ignoring errors, hidden rows, and other functions that may appear in data.There are two reasons that make the AGGREGATE function especially useful, compared to other functions which perform the same operations: The AGGREGATE function returns the result of an aggregate calculation like AVERAGE, COUNT, MAX, MIN, etc.