Aggregate function includes multiple mathematical functions such as average, sum, max, min, mean, and so on, as well as the conditions that are customized for each of these sub-functions. We can even ignore specific cells or rows, such as a blank row, an error value, and so on, and still get the desired result. In the Aggregate function, we have a total of 19 sub-functions with various Options.
Definition:
“Returns the AGGREGATE in a database of values or list”
It means that it performs a variety of calculations (19 Excel statistical functions)
1 to 13 are Reference form, and 14 to 19 are array form
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
Options: is a number ranging from 0 to 7 that indicates which values to ignore for the aggregate function. If the options parameter is not specified, the default value is 0.
0: Ignore nested SUBTOTAL and AGGREGATE functions
1: Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows
2: Ignore nested SUBTOTAL, AGGREGATE functions, and error values
3: Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows and error values
4: Ignore nothing
5: Ignore hidden rows
6: Ignore error values
7: Ignore hidden rows and error values
When using the REFERENCE syntax, it is the first numeric argument for the function. We want to perform the computation on values or numeric values. Here, a minimum of two arguments are required; the remaining arguments are optional; for the second reference, the numerical arguments should be between 2 and 253 for which the aggregate value is desired.
Array: An array or refers to a range of cells when using the ARRAY syntax
[k]: The last 6 functions (under 1 to 19 function list): k value as a fourth argument
It is an optional argument that is used when using the ARRAY syntax to determine LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC, or QUARTILE.EXC.
Please Watching My Video is Below
No comments:
Post a Comment