AGGREGATE function in Excel

What is the AGGREGATE function in Excel?

The AGGREGATE function is one of the math functions of Excel.

This function has multiple argument lists. Please select one of them.

Arguments:

Function_num,options,array,k
Function_num,options,ref1,…

We can find this function in the Math&trig category of the insert function Tab.

How to use AGGREGATE function in excel

1. Click on an empty cell (like F5 )

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select math and trig category.

5. Select the AGGREGATE function.

6. Then select ok.

7. In the function arguments Tab you will see the AGGREGATE function.

8. Function_num is the number 1 to 19 that specifies the summary function for the aggregate.

9. Options is the number 0 to 7 that specifies the values to ignore for the aggregate.

10. Array: is the array or range of numerical data on which to calculate the aggregate.

11. K: indicates the position in the array; it is k-th largest, k-th smallest, k-th percentile, or k-th quartile.

12. Ref1: ref1,ref2,… are 1 to 253 ranges or references for which you want the aggregate.

Examples of AGGREGATE function in excel

Example 1:

How to use AGGREGATE function in excel

You can see examples of python code for AGGREGATE function below:

python code for the AGGREGATE function

import pandas as pd

df =pd.read_csv("example.csv") 

out=df.aggregate({"Height":['max', 'min'],"Age":['max', 'min'],"Weight":['min', 'max']})

print(out)

Height Age Weight

max 210 29 240
min 195 21 180

What is the purpose of AGGREGATE function?

This function has multiple argument lists.

What is the Return value of AGGREGATE function?

It returns a number.

How many arguments does AGGREGATE function have?

This function has just 1 Argument.

The argument of AGGREGATE function is number.

Function_num is the number 1 to 19 that specifies the summary function for the aggregate.

The argument of AGGREGATE function is required and not optional.

AGGREGATE(number) = number

Which version of excel supports AGGREGATE function?

This function is available for all excel versions (2003-2019).

Errors in AGGREGATE function

AGGREGATE related functions :

• AVERAGE function
• SUBTOTAL function
• COUNT function