The following is a list of the Aggregate Functions available to the user during Group By shaping operations. Click the link to learn more about each individual function:
...
Array
Average
Count
Count (Numbers Only)
Count Distinct
First
Last
Max
Min
Median
Mode
Stdev
Stdevp
Sum
Var
Varp
...
| Anchor | ||||
|---|---|---|---|---|
|
Of the available aggregate functions available, array is unique in that it operates on both text and numeric values. Rather than perform a mathematical operation on collapsed rows, all values in the reference column (the column to which array is applied) are temporarily stored. As the unique single row is created, array assembles the reference column data from the set into a single, comma-separated string within the column.
Excluding the column to which the array is being applied, all rows are examined column-by-column in order to find identical rows. The header of the reference column receives a name change to become “Array of <column name>”.
Examples
The small dataset below will be used to show how array operates.
| Column A | Column B | Column C |
|---|---|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
...
Related aggregate functions are Median and Mode.
Examples
The small dataset below will be used to show how average operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
...
The First function searches the data for duplicate rows (based on the columns chosen by the user) and condenses them into a single row of data. The First function will then display the first value within the data that appeared for the duplicate rows. Any values following the first value will be lost during this process.
Example
The small dataset below will be used to show how First operates.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 5 |
| one | two | 6 |
| two | two | 7 |
| one | two | 4 |
...
The Last function searches the data for duplicate rows (based on the columns chosen by the user) and condenses them into a single row of data. The Last function will then display the last value within the data that appeared for the duplicate rows. Any values prior to the last value will be lost during this process.
Example
The small dataset below will be used to show how Last operates.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 5 |
| one | two | 6 |
| two | two | 7 |
| one | two | 4 |
...
The counterpart to this function is Min (Minimum). See the next section for details.
Example
The small dataset below will be used to show how max operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
...
The counterpart to this function is Max (Maximum). See the previous section for details.
Example
The small dataset below will be used to show how min operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
...
Related aggregate functions are Average and Mode.
Example
The small dataset below will be used to show how median operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 5 |
| one | two | 6 |
| two | two | 7 |
| one | two | 4 |
...
Related aggregate functions are Average and Median.
Example
The small dataset below will be used to show how mode operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 3 |
| one | two | 6 |
| two | two | 7 |
| one | two | 3 |
...
The standard deviation for data is the square root of its Var. If the set under analysis represents all data points (referred to as a “population”) use of Stdevp is recommended for a more accurate result. A related function that deals with statistical variance is Varp.
Example
The dataset below will be used to show how stdev operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 0.2 |
| one | two | 0.1 |
| one | two | 1.1 |
| one | two | 0.2 |
| one | two | 0.6 |
| one | one | 0.2 |
| one | one | 0.27 |
| one | two | 0.2 |
| one | two | 0.4 |
...
If the set under analysis represents only a sample of data, use of Stdev is recommended for a more accurate result. Other related functions that deal with statistical variance are Var and Varp.
Example
The dataset below will be used to show how stdevp operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 0.2 |
| one | two | 0.1 |
| one | two | 1.1 |
| one | two | 0.2 |
| one | two | 0.6 |
| one | one | 0.2 |
| one | one | 0.27 |
| one | two | 0.2 |
| one | two | 0.4 |
...
This aggregate function executes an addition operation on the numbers in the reference column (the column to which sum is applied) as all duplicate rows are collapsed into unique single rows. Excluding the column to which the sum is being applied, all rows are examined column-by-column in order to find identical rows. Note that attempts to apply sum to a text value in the reference column will result in a 0 for that row. The header of the reference column receives a name change to become “Sum of <column name>”.
Examples
The small dataset below will be used to show how sum operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| 1 | two | 5 |
| 1 | two | 6 |
| two | two | 7 |
| 1 | two | 4 |
...
| Anchor | ||||
|---|---|---|---|---|
|
Estimates the how much dispersion exists (how much the values are spread out) within a sample set of data. This aggregate function calculates the variance of the numeric values in the reference column (the column to which var is applied) among those rows that are identical.
All duplicate rows are discovered by conducting a column-by-column examination (excluding the column to which var is being applied) before they are collapsed into unique single rows. For each resulting single row, the value in the reference column from the contributing duplicate row becomes part of the variance calculation. The header of the reference column receives a name change to become “Var of <column name>”.
...
If the set under analysis represents all data points (referred to as a “population”) use of Varp is recommended for a more accurate result. Related functions that deal with statistical variance are Stdev and Stdevp.
Example
The dataset below will be used to show how var operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 0.2 |
| one | two | 0.1 |
| one | two | 1.1 |
| one | two | 0.2 |
| one | two | 0.6 |
| one | one | 0.2 |
| one | one | 0.27 |
| one | two | 0.2 |
| one | two | 0.4 |
...
If the set under analysis represents only a sample set of data, use of Var is recommended for a more accurate result. Related functions that deal with statistical variance are Stdev and Stdevp.
Example
The dataset below will be used to show how varp operates. All numbers are numeric values (not text) in order to illustrate behavior of this aggregate function.
| Column A | Column B | Column C |
|---|---|---|
| one | two | 0.2 |
| one | two | 0.1 |
| one | two | 1.1 |
| one | two | 0.2 |
| one | two | 0.6 |
| one | one | 0.2 |
| one | one | 0.27 |
| one | two | 0.2 |
| one | two | 0.4 |
...