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 )
Click on an empty cell

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

Click on fx icon (or press shift+F3)

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

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.

 Select the AGGREGATE function

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.

Function_num is the number 1 to 19

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

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.

How to use AGGREGATE function in excel

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

NameAgeHeightWeight
Olivia25210180
Noah25205235
Oliver27195205
Elijah22198185
James29199231
William29201240
Benjamin21202235
Lucas25200238
Henry22204190

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 :

Leave a Reply

Your email address will not be published. Required fields are marked *