The metrics parameter

What it does

Available metrics for usage: sum,mean,slope,sumOfSquares,variance,std,cv,skewness,kurtosis,geometricMean,harmonicMean,flatness,median,q1,q3,p10,p90,relativeDispersion,iqr,range,min,max,percentile,zScore,n,mad,weight,sumCenteredSquares,spd,spectralPowerDensityMetric,stdDev,missing

The folowing metrics, needs column name as parameter: weightedMean,wmean,regressionSlope,rSlope

Every metric is computed on the valid values of the column (numbers; text with value labels counts by its codes).

A metric that is not defined for the data is an empty cell, as in SPSS, not 0.

Scalar metrics:

• sum — The sum of the values; empty for a column without a single number (the sign . in a STATS_COMPUTE row where no item is answered), not 0.
• mean — The arithmetic mean: the sum divided by n.
• median — The middle of the sorted values; with an even n, the mean of the two middle ones.
• n — The number of valid values of the column: numbers, and text by its codes when it has value labels.
• min — The smallest value.
• max — The largest value.
• range — max − min.
• missing — The number of empty answers and of cells that do not match the column type (text in a number column), inside the selected rows.
• variance — The mean squared deviation from the mean: sample formula (n − 1); with pop = TRUE in Settings, population (n).
• std, stdDev — The square root of the variance: sample formula (n − 1); with pop = TRUE in Settings, population (n).
• cv — std / mean; empty when the mean is 0.
• relativeDispersion — std / median; empty when the median is 0.
• skewness — The asymmetry of the distribution, with the sample correction as in Excel and SPSS; empty for fewer than 3 values or a column of equal values.
• kurtosis — Excess kurtosis: how heavy the tails are compared with the normal distribution (0), with the sample correction as in Excel and SPSS; empty for fewer than 4 values or a column of equal values.
• q1 — The first quartile: 25% of the values are below it; computed as in SPSS, so it can differ from the Sheets QUARTILE and PERCENTILE functions.
• q3 — The third quartile: 75% of the values are below it; computed as in SPSS, so it can differ from the Sheets QUARTILE and PERCENTILE functions.
• p10 — The 10th percentile: 10% of the values are below it; computed as in SPSS, so it can differ from the Sheets QUARTILE and PERCENTILE functions.
• p90 — The 90th percentile: 90% of the values are below it; computed as in SPSS, so it can differ from the Sheets QUARTILE and PERCENTILE functions.
• iqr — q3 − q1.
• percentile.<q> — The q-th percentile, q from 0 to 100, fractions allowed (percentile.12.5); computed as in SPSS, like the quartiles. percentile.0.95 is the 0.95th percentile, not the 95th. Without q or with q outside 0…100 the cell is empty and the error is listed under the table.
• mad — The median of the absolute deviations from the median, without the 1.4826 factor.
• geometricMean — The n-th root of the product of the values; zero and tiny values are raised to epsilon (Settings), a negative value gives an empty cell.
• harmonicMean — n divided by the sum of 1/x; zero and tiny values are raised to epsilon (Settings), a negative value gives an empty cell.
• flatness — The geometric mean of the values as they are divided by their arithmetic mean, without epsilon: a zero among the values gives exactly 0; empty when the mean is 0 or a value is negative.
• spd, spectralPowerDensityMetric — The geometric mean divided by the arithmetic mean of the shares x² / Σ x².
• slope — (last value − first value) / (n − 1), in row order; empty for fewer than 2 values, 0 for a constant.
• sumOfSquares — The sum of the squared values: Σ x²; empty for a column without a single number.
• sumCenteredSquares — The sum of the squared deviations from the mean: Σ (x − x̄)²; empty for a column without a single number, 0 for one value.
• weight — n / variance; empty for a column without spread or with fewer than 2 values.
• zScore.<v> — The z-score of the value v: (v − mean) / std; 0 for a column without spread; empty without v.

Metrics with a column as parameter (pairs are taken row by row, only rows with a number in both columns):

• weightedMean.<weightsCol>, wmean.<weightsCol> — The weighted mean with the weights in the column weightsCol, row by row; a zero, negative or missing weight leaves the row out, as SPSS WEIGHT. Empty when no row has a usable weight.
• regressionSlope.<xCol>, rSlope.<xCol> — The least-squares slope of this column on the column xCol, on the rows where both are numbers; empty with fewer than 2 rows or without spread of X.

Values

Required.

In plain words

A teacher had 120 survey answers and chose which numbers to see. With the text mean, min, max, n, she got 3.42 for the average, 1 and 5 for the smallest and largest answers, and 120 for the number of answers. With median, q1, q3, she got 3.5, 3 and 4, so the middle half of the students answered between 3 and 4. Each name asks for one row of the table, and she can ask for as many as she likes in one go. A number that cannot be worked out, such as for a column where everyone answered the same, is left empty, not shown as 0.

Where you use it

• STATS_DESCRIPTIVE — Column-wise descriptive statistics with flexible metric selection and optional column filtering.

Examples

• sum,mean,slope,sumOfSquares,variance,std,cv
• wmean.colName,rSlope.colName — Metrics with column name as parameter
• "mean,std,n" — mean, std and n in one table, a column per selected column
• "q1,median,q3,percentile.95,percentile.12.5" — quartiles and percentiles as in SPSS
• "weightedMean.w,regressionSlope.x" — weighted mean with the weights column w, slope on the column x

Get it in your sheet

ALS Stats is a free add-on for Google Sheets™.

Install ALS Stats from Google Workspace Marketplace (opens in a new tab)