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 |
