Aggregate: Average

[Akshay1]

This aggregate function finds an average of the numbers in the reference column (the column to which average is applied) as all duplicate rows are collapsed into unique single rows. Excluding the column to which the average is being applied, all rows are examined column-by-column in order to find identical rows.

A mathematical average (also called an “arithmetic mean” or simply “mean”) is calculated by adding all of the numeric values in a set and then dividing the resulting sum by the number of items the set contained. Note that attempts to apply average 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 “Average of <column name>”.

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

Example 1

Applying the average function to Column C reduces the row count from four to two. The value in the column Average of Column C shows the average of of the Column C values in the duplicate rows that were collapsed during the operation: (5 + 6 + 4) ÷ 3 = 5 while 7 ÷ 1 = 7.

Column A	Column B	Average of Column C
1	two	5
two	two	7

Example 2

Applying the average function to Column A results in no row count reduction since examination of Column B and Column C values in each row reveals that each is already unique. The value in the column Average of Column A therefore displays each row with its original numeric value (in this case, a number 1) since the average function found no duplicate rows that could be involved in a mathematical operation. The 0 replaces the text “two” because the average operation cannot be applied to text values.

Average of Column A	Column B	Column C
1	two	5
1	two	6
1	two	4
0	two	7