Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Aggregate

Excel AGGREGATE function

From Excel kitchen comes new AGGREGATE function. This function appeared yet in Excel 2010, so we can use in Excel 2013. In this tutorial, I will try to explain the basics of Excel Aggregate function. Aggregate functions not available in earlier versions of Excel so that you can not use because it will return error #NAME?

Excel Aggregate function, in principle, primarily as a result returns the sum of a list or database. An AGGREGATE function can be applied to different functions of addition, with the option to ignore hidden rows and the value of errors. Also, Excel Aggregate function can be used to improve the Conditional Formatting restrictions. If you use the Conditional Formatting (CF) and to a certain range and the range of data you have error (such as #REF!, #DIV/0!, #NAME?). You in this case you can use this Excel AGGREGATE function and you can ignore the error. It is known that Excel SUBTOTAL function ignores hidden rows, though Excle Aggregate function is more advanced to use.

Aggregate function have two versions (forms/formats) syntax, the formula.

SINTAX REFERENCE

=AGGREGATE(function_num,options, ref1,[ref2],…)

SINTAX ARRAY

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


function_num (A first argument is a number. This number is required and it can be from 1 to 19. It depends on what function you want to use. Similar to the functions Subtotal, is set aside in whole numbers. For details, see Table1 below).

options (the second argument is a number. This number is required and it can be from 0 to 7. If you omit this number function will by default use the number 0 (zero). This number is determined, that the value of the function ignored in the specified range of cells. List see Table2 below).

ref1 (The third argument is the number. This number is required in the Syntax Reference formula, and is related to multiple numeric arguments that calculate aggregate value).

[ref2] (fourth argument is a number. This number is not required. This number can be from 2 to 253),

array (In Syntax Array formula this third argument is required. This ARRAY argument refers to a range of cells when using a formula Array).

[k] (fourth argument Syntax Array formula is optional. Attention! If you do use this argument, then you see in Table3 below which functions are required with this argument).

Table 1

function_num argument.

Aggregate function in Excel - function_num argument
Table 2

options argument.
Aggregate function in Excel - options argument
Table 3

k argument.
Aggregate function in Excel - 'k' argument

Example Aggregate function in Excel

Take for example this reference formula in the relation to the other formula which have one more argument

=AGGREGATE(4;6;A1:A11)
  • The formula returns the highest value in the range of data which is in this case A1:A11.
  • The first argument is the number 4 and in Table 1 we can see that this is equivalent to the MAX function. If we use the formula =MAX(A1:A11) result would be a mistake because in the range of data for at least one cell contains an error.
  • The second argument is the number 6 and in Table 2 we can see that this number means that the formula ignores the error range data.
  • The third argument is the data range.
Now let's take this other formula that is similar to the above formula, but there is one argument more.

=AGGREGATE(4;6;A1:A11;2)
  • The first argument is the number 4 and in Table 1 we can see that this is equivalent to the MAX function. When to use the formula = MAX (A1:A11), the result would be a mistake.
  • The second argument is the number 6 and in Table 2 we can see that this number means that the formula ignores the error range data.
  • The third argument is the data range.
  • The fourth argument is the number 2. This means that we are looking for second highest value. For the third highest value of this number would be 3, and so on.
Aggregate function in Excel - examples

So, if you intend to use this Excel AGGREGATE function, you need to be based on the values (numbers) in the tables above. See example AGGREGATE function in the image below. Also see another tutorial, in which I used the AGGREGATE function. How to display the latest date for a particular group code or ID numbers.

AGGREGATE function in Excel example and see the arguments