Ace My Paper - get your Excel tasks done by experts.

Assignmentgeek.com - makes your homework shine. # How To Use Aggregate Function In Excel

## 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. 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.

Excel 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 (The 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 (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 (screenshot below)

function_num argument. Table 2 (screenshot below)

options argument. Table 3 (screenshot below)

[k] argument. # Example Aggregate Function in Excel

Take for Excel Example Tutorials 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 the range of data which is in the 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. So, if you intend to use this example of Excel AGGREGATE function, you need to be based on the values (numbers) in the the tables above. See example AGGREGATE function in the image below. Also see another tutorial, in which I used AGGREGATE function. How to display the latest date for a particular group code or ID numbers. 