AGGREGATE

Returns calculated Aggregate values.
Returns calculated Aggregate values.

Rational.

To give the value of the function used. 

Description

The Excel AGGREGATE Function returns a value after calculating based on the function from Sum, Average, etc (1 to 19) you applied and the options used for ignorance. 
For example, suppose that you have a number with few cells empty and contained error in a column and the user wants to find the sum, but some function will show an error. For this we will use Aggregate the formula can be =AGGREGATE(B7,C7,D4:D10) the Excel numbers add and returns 124.

Syntax

Aggregate(function_num, options, array, [k])

Parameters

Function_num– The number which divides

options – Selects numbers for ignorance (1 to 7).

Array – The range of cells.

K [optional]– It is the second reference (k).

Return Value

Aggregate value

Key Notes

  • The Aggregate function shows error #VALUE! in a cell, if the second argument i.e. k required but not given in the formula. Like in cell E9 if the k is not given it shows error.
  • The Aggregate function shows error #N/A in a cell if the range contained an error and you did not select options of numbers for ignorance.

                                       Numbers  Stands for Function

Function_num

Function

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

6

PRODUCT

7

STDEV.S

8

STDEV.P

9

SUM

10

VAR.S

11

VAR.P

12

MEDIAN

13

MODE.SNGL

14

LARGE

15

SMALL

16

PERCENTILE.INC

17

QUARTILE.INC

18

PERCENTILE.EXC

19

QUARTILE.EXC

 

The options (1 to 7) used to remove the error, hidden rows, etc in brief

Option

Behavior

0

Ignore SUBTOTAL and AGGREGATE functions

1

Ignore hidden rows, SUBTOTAL and AGGREGATE functions

2

Ignore error values, SUBTOTAL and AGGREGATE functions

3

Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions

4

Ignore nothing

5

Ignore hidden rows

6

Ignore error values

7

Ignore hidden rows and error values