Description
Returns an aggregate in a list or database.
NOTE
- The [number] can either be numbers or names, arrays, or references that contain numbers.
Syntax
Copied!=AGGREGATE(function_num, options, ref1, [ref2], ...)
rULES
| Num | Function | Option | Behavior |
|:---:|:---------------|:------:|:------------------------------------------------------------------|
| 1 | AVERAGE | 0 | Ignore Nested [subtotal and aggregate] |
| 2 | COUNT | 1 | Ignore Hidden rows, nested [subtotal and aggregate] |
| 3 | COUNTA | 2 | Ignore Error values, nested [subtotal and aggregate] |
| 4 | MAX | 3 | Ignore Hidden rows, error values, nested [subtotal and aggregate] |
| 5 | MIN | 4 | Ignore Nothing |
| 6 | PRODUCT | 5 | Ignore Hidden rows |
| 7 | STDEV.S | 6 | Ignore Error values |
| 8 | STDEV.P | 7 | Ignore Hidden rows and error values |
| 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 | | |SAMPLE DATA
| | A | B | C | D | E |
|:---:|:----|:----|:----|:----|:----|
| 1 | | | A | | |
|-----|-----|-----|-----|-----|-----|
| 2 | | | B | | |
|-----|-----|-----|-----|-----|-----|
| 3 | | | 4 | | |
|-----|-----|-----|-----|-----|-----|
| 4 | | | D | | |
|-----|-----|-----|-----|-----|-----|
| 5 | | | 8 | | |
|-----|-----|-----|-----|-----|-----|
| 6 | | | | | |
|-----|-----|-----|-----|-----|-----|
| 7 | | | 2 | | |
|-----|-----|-----|-----|-----|-----|
| 8 | | | E | | |
|-----|-----|-----|-----|-----|-----|
| 9 | | | | | |EXAMPLE 1
Copied!//𝘧𝘪𝘯𝘥 𝘵𝘩𝘦 𝘱𝘰𝘴𝘪𝘵𝘪𝘰𝘯 𝘰𝘧 𝘵𝘩𝘦 𝘭𝘢𝘴𝘵 𝘰𝘤𝘤𝘶𝘳𝘳𝘪𝘯𝘨 𝘯𝘶𝘮𝘣𝘦𝘳 𝘪𝘯 𝘵𝘩𝘦 𝘳𝘢𝘯𝘨𝘦 C1:C9 =AGGREGATE(14, 6, ROW(C1:C9)/ISNUMBER(C1:C9), 1) -> 𝘳𝘦𝘵𝘶𝘳𝘯 8