##### Page tree

Release 8.7

Contents:

Computes the average (mean) from all row values in a column or group. Input column can be of Integer or Decimal.
• If a row contains a missing or null value, it is not factored into the calculation. If the entire column contains no values, the function returns a null value.
• When used in a `pivot` transform, the function is computed for each instance of the value specified in the `group` parameter. See Pivot Transform.

For a version of this function computed over a rolling window of rows, see ROLLINGAVERAGE Function.

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

## Basic Usage

average(myRating)

Output: Returns the average of the values in the `myRating` column.

## Syntax and Arguments

average(function_col_ref) [group:group_col_ref] [limit:limit_count]

ArgumentRequired?Data TypeDescription
function_col_refYstringName of column to which to apply the function

For more information on the `group` and `limit` parameters, see Pivot Transform.

For more information on syntax standards, see Language Documentation Syntax Notes.

### function_col_ref

Name of the column the values of which you want to calculate the average. Column must contain Integer or Decimal values.

• Literal values are not supported as inputs.
• Multiple columns and wildcards are not supported.

Usage Notes:

Required?Data TypeExample Value
YesString (column reference)`myValues`

## Examples

Tip: For additional examples, see Common Tasks.

### Example - Statistics on Test Scores

This example illustrates how you can apply statistical functions to your dataset. Calculations include average (mean), max, min, standard deviation, and variance.

Source:

Students took a test and recorded the following scores. You want to perform some statistical analysis on them:

StudentScore
Anna84
Ben71
Caleb76
Danielle87
Evan85
Faith92
Gabe85
Hannah99
Ian73
Jane68

Transformation:

You can use the following transformations to calculate the average (mean), minimum, and maximum scores:

Transformation Name `New formula` `Single row formula` `AVERAGE(Score)` `'avgScore'`

Transformation Name `New formula` `Single row formula` `MIN(Score)` `'minScore'`

Transformation Name `New formula` `Single row formula` `MAX(Score)` `'maxScore'`

To apply statistical functions to your data, you can use the `VAR` and `STDEV` functions, which can be used as the basis for other statistical calculations.

Transformation Name `New formula` `Single row formula` `VAR(Score)` `var_Score`

Transformation Name `New formula` `Single row formula` `STDEV(Score)` `stdev_Score`

For each score, you can now calculate the variation of each one from the average, using the following:

Transformation Name `New formula` `Single row formula` `((Score - avg_Score) / stdev_Score)` `'stDevs'`

Now, you want to apply grades based on a formula:

Gradestandard deviations from avg (stDevs)
AstDevs > 1
BstDevs > 0.5
C-1 <= stDevs <= 0.5
DstDevs < -1
FstDevs < -2

You can build the following transformation using the `IF` function to calculate grades.

Transformation Name `New formula` `Single row formula` `IF((stDevs > 1),'A',IF((stDevs < -2),'F',IF((stDevs < -1),'D',IF((stDevs > 0.5),'B','C'))))`

To clean up the content, you might want to apply some formatting to the score columns. The following reformats the `stdev_Score` and `stDevs` columns to display two decimal places:

Transformation Name `Edit column with formula` `stdev_Score` `NUMFORMAT(stdev_Score, '##.00')`

Transformation Name `Edit column with formula` `stDevs` `NUMFORMAT(stDevs, '##.00')`

Transformation Name `New formula` `Single row formula` `MODE(Score)` `'modeScore'`

Results:

Anna8485826899

87.00000000000001

9.330.21C
Ben718582689987.000000000000019.33-1.18D
Caleb768582689987.000000000000019.33-0.64C
Danielle878582689987.000000000000019.330.54B
Evan858582689987.000000000000019.330.32C
Faith928582689987.000000000000019.331.07A
Gabe858582689987.000000000000019.330.32C
Hannah998582689987.000000000000019.331.82A
Ian738582689987.000000000000019.33-0.96C
Jane688582689987.000000000000019.33-1.50D