AGGREGATE Function in Excel

Syntax of the AGGREGATE Function

The two syntaxes of the function are stated as follows:

Array Syntax

“=AGGREGATE(function_num,options,array,[k])”

Reference Syntax

“=AGGREGATE(function_num,options, ref1, ref2, ref[3],…)”

The following additional arguments are used in array syntax:

  • Array: This is an array of values on which we want to operate.K: This is a numeric value used with functions like LARGE, SMALLSMALLThe SMALL Function in Excel returns the nth smallest value from a set of values. For example, 2nd smallest value, 3rd smallest value and so on.read more, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC, and QUARTILE.EXCQUARTILE.EXCQuartile functions are used to find the various quartiles of a data set and are part of Excel’s statistical functions. There are three quartiles; the first quartile (Q1) is the middle number between the smallest value and the median value of a data set. The second quartile (Q2) is the median of the data. The third quartile (Q3) is the middle value between the median of the data set and the highest value.read more.

The “array” is a mandatory argument and “k” is an optional argument.

The function accepts the following arguments:

Function_num: This number represents a specific function that is to be used. It ranges from 1-19.

Options: This is a numeric value ranging from 0 to 7. It determines the values that are ignored during calculations.

Ref1, ref2, ref[3]: This is the numeric value or values on which computations are to be performed.

It is mandatory to provide at least two of these arguments, and the remaining arguments are optional.

The Characteristics of the AGGREGATE Function

The features of the AGGREGATE function are listed as follows:

  • It does not recognize the function_num value greater than 19 and lesser than 1.For Option Number, the AGGREGATE function does not identify the values greater than 7 and lesser than 1.If we feed any other values, it gives a “#VALUE! error#VALUE! Error#VALUE! Error in Excel represents that the reference cell the user has either entered an incorrect formula or used a wrong data type (mostly numerical data). Sometimes, it is difficult to identify the kind of mistake behind this error.read more.”It always accepts a numeric value and always returns a numeric value as the output.It ignores only the hidden rows but does not ignore the hidden columns.

Examples

Example #1

We have a list of numbers, and we want to compute the Average, Count (the number of cells that contain a value), CountaCountaThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more (count of cells that are not empty), Maximum, Minimum, Product, and Sum. The values are given in the following table:

Let us first calculate the Average in the ninth row for all the given values. For Average, the function_ num is 1.

The values are given in column C. Since no values are ignored, we select option 4 (i.e., ignore nothing).

We select the range of values C1:C8.

We calculate the Average by omitting the value of “k.”

The average value is 5.25, as shown in the succeeding image.

Similarly, for range D1:D8, we select option 4.

In range E1:E8, cell E6 contains an error value. If we use the same AGGREGATE formula, we get an error. If an appropriate option is used, the AGGREGATE function gives the average of the remaining values while neglecting the error in E6.

To ignore the error values, we select option 6.

Similarly, for range G1:G8, we use option 6 (ignore the error values).

For cell H3, we enter the value 64, hide the third row, and use option 5 to ignore the hidden row. The AGGREGATE function gives the average value of the numeric values that are visible.

The output without hiding the third row is shown in the succeeding image.

The output after hiding the third row is shown in the succeeding image.

We apply the AGGREGATE formula to other operations, as shown in the following image.

Example #2

A table shows the revenueRevenueRevenue is the amount of money that a business can earn in its normal course of business by selling its goods and services. In the case of the federal government, it refers to the total amount of income generated from taxes, which remains unfiltered from any deductions.read more generated on different dates from the different channels. We want to find the revenue generated from the different channels.

We apply the SUM functionSUM FunctionThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more to get the total revenue generated.

If we want to check the revenue generated from “Organic Channel” or “Direct Channel,” we apply filters in Excel. The SUM function gives the total sum.

To get the sum of the visible values when a filter is applied, instead of using the SUM function, we will use the AGGREGATE function. So, on replacing the SUM formula with an AGGREGATE function with option code 5 (ignoring the hidden rows and values); we have,

We apply the filter to different channels. It shows the sum of revenue for the filtered channel. The rest of the rows are hidden.

Total Revenue Generated for Direct Channel is shown in the succeeding image:

Total Revenue Generated for Organic Channel is shown in the succeeding image:

Total Revenue Generated for Paid Channel is shown in the succeeding image:

The AGGREGATE function calculates the different SUM values for the revenue generated from different channels. The AGGREGATE function is dynamic in nature. It is used to replace different functions for different conditions without using the conditional formula.

Let us assume that in the same table, some of the revenue values contain an error. We want to ignore the errors. At the same time, if we apply a filter, the AGGREGATE function should ignore the hidden row values.

When we use option 5, we get an error as the sum of the total revenue. With the usage of option 6, the SUM function ignores error values.

When we apply the filter (for example, filter by channel value Direct), the SUM function ignores the errors. However, we also need to ignore the hidden values.

In this case, we use option 7. This ignores the error values and the hidden rows simultaneously.

The AGGREGATE function in Excel allows applying different aggregate functions like AVERAGE, SUM, PRODUCT, COUNT, COUNTA, MAX, or MIN to a list of data, with an option to ignore hidden rows and error values.

An AGGREGATE function in Excel does a calculation on a set of values and returns a single value. Except for COUNT, the AGGREGATE functions ignore null values.

An AGGREGATE function, a built-in function in Excel, is categorized as a Math or Trig Function. It finds its usage as a worksheet function (WS) in Excel. As a WS function, the AGGREGATE function can be entered as part of a formula in a cell of a worksheet. Microsoft created it to address the limitations of conditional formatting.

Key Takeaways

  • AGGREGATE function in Excel returns the aggregate of data provided in a table or data list.

  • In the AGGREGATE function, the first argument is function number, and further arguments are for a range of the data sets.

  • The reference syntax of the AGGREGATE formula is “=AGGREGATE(function_num, options, ref1, ref2, ref[3],…)”

  • The Array syntax of AGGREGATE formula is “=AGGREGATE(function_num,options,array,[k])”

  • “k” is an optional argument used with functions like LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC, and QUARTILE.EXC.

  • The AGGREGATE function is often used to replace different functions for different conditions without using the conditional formula.

AGGREGATE Excel Function Video

Guide to the AGGREGATE function in Excel. Here, we discuss how to use AGGREGATE formula along with step by step example. You may also look at these useful functions in Excel –

  • REPLACE Function in ExcelREPLACE Function In ExcelThe Replace function is a text function that replaces an old string with a new one. The input required is the old text, new text, and the starting and ending numbers of the characters that need to be replaced.read morePOWER Function in ExcelPOWER Function In ExcelPOWER function calculates the power of a given number or base. To use this function you can use the keyword =POWER( in a cell and provide two arguments one as number and another as power.read moreIF FunctionIF FunctionIF function in Excel evaluates whether a given condition is met and returns a value depending on whether the result is “true” or “false”. It is a conditional function of Excel, which returns the result based on the fulfillment or non-fulfillment of the given criteria.
  • read moreTrue Function in ExcelTrue Function In ExcelIn Excel, the TRUE function is a logical function that is used by other conditional functions such as the IF function. If the condition is met, the output is true; if the conditions are not met, the output is false.read more