STATS_DESCRIPTIVE – column-wise descriptive statistics

Cell range with data, e.g., A1:D100 or A:D.

Usually the first row should contain column names.

You can also add labels as the first row and column names as the second row.

Labels should use formats like "male:1,female:2" or "low:1:3,mid:3:5,high:5:7".

Examples:

• A1:D100 — use 4 columns
• A1:C200 — use 3 columns
• A:F — use 6 columns
• A1:D100 where row 1 contains labels and row 2 contains column names — use labels from the sheet

If every non-empty cell of the first row holds at least two label rules ("low:1,high:2"), it is the labels row, and the next row holds the column names; an empty cell there is a column without labels.

One rule in a cell ("yes:1") is read as a name, so a header such as "T:1", "Ratio 1:2" or "Note:" stays the names row. Give such a column a second rule, or pass one label in the labels parameter, where one rule is valid.

If the first row contains ordinary text, it is treated as the column names row as before.

A number, a date or a checkbox in the names row is a name: 2025, 2025-01-01, TRUE.

Column names must be unique: two identical headers are an error "Duplicate column name: x".

Every column with values needs a name: an empty header cell over values is an error "The name of column 2 is empty: a column with values needs a name", even when colFilter does not select that column. A column with neither a name nor values (the empty right edge of a range such as A:Z) is dropped without an error.

Column names that start with "#" are reserved/unsupported in filters, because "#" is used for index-based colFilter rules (for example #1, #2-#5).

What counts as a number (the same in every function):

• A column is numeric when it holds more numbers than text; with as many texts as numbers, or more, it is a text column.
• Every cell is typed by itself: a number written as text ('10', '007') is the number, a checkbox is 1 (checked) or 0 (unchecked). The texts 'TRUE'/'FALSE' stay text.
• A cell that does not match the type of its own column (text in a numeric column, a number in a text column) is missing for calculations and is counted in missing.
• An empty cell is a missing answer; the sign "." means "not applicable" — it is not a missing answer, not a category and not counted in missing.
• A text column with value labels (a labels row above the names) is analysed by the codes of its labels. A text column without labels is not analysed as numbers: lists of columns skip it with a note, tests on a pair of columns and models show an error.

An empty tail of the range (rows below the data) is dropped — only blank cells make it: a row of "." at the end stays and counts as not applicable, as in the middle. A column with neither a name nor values is dropped; a named empty column stays.

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

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

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.

Comma-separated column selector.

Use names ("age,score"), negative rules ("-id,-note"), a regex ("/^q/"), or index rules ("#N", "#A-#B", "-#N", "-#A-#B").

Case-sensitive; flags are not supported.

A name that does not exist is an error: "Column nope does not exist" (excluding it, "-nope", is ignored). Empty — all columns ("*").

Examples:

• "age,score" — Names only (include only "age" and "score")
• "-id,-group" — Exclude some (include all except "id" and "group")
• "/^q/" — Regex only (all starts with "q")
• "#1,#3-#5" — Index include rules (first, and range 3..5)
• "-#1,-#3-#4" — Index exclude rules (drop first and 3..4)
• "age,#2-#4,-#3" — Mixed name/index rules with deterministic order
• "age,/^q/,-q5" — Mixed (include age, all starts with "q" except "q5")
• "/^x1{1,2}$/" — Regex with a comma inside: x1 and x11

Choose which columns from columns to use - a comma-separated string of rules.

⚙️ Four rule types (you can mix them):

1. By name - exact header text (case-sensitive)
   • "age,score,total"
2. Negative rules - exclude by name (prefix with -)
   • "-id,-notes" (include all except id and notes)
3. Regex - a regular expression between slashes /.../
   • "/^q/" (all headers starting with q), "/(score|age)/", "/^x1{1,2}$/" (commas inside /.../ do not split the filter)
   • ❗— case-sensitive
   • ❗— flags are not supported.
4. Index rules - position-based rules with #
   • "#1" (first selected column), "#2-#5" (range), "-#1", "-#2-#4"

"*" alone selects every column; it cannot be combined with other rules ("*,-id" is an error) — write "-id" instead.

Rules are applied in the written order, and the selected columns keep that order ("/^q/,-q5,age" puts age last).

Index rules count the columns of the range: "#1" is its first column.

A name that does not exist is an error: "Column nope does not exist" — also in colName, yName, steps, rowCol, colCol and colNameForSplit. Excluding a name that does not exist ("-nope") is ignored.

A filter that selects no column — a regex without a match ("/^Q/" where the columns are q1, q2), index rules past the end, exclusions that remove every column — is an error that names the filter and the columns of the range: colFilter "/^Q/" selects no columns (columns: q1, q2, t). Empty and "*" select every column.

In STATS_ANALYZE, a regex with brackets or | needs the new syntax: PEARSON('/(score|age)/').

A colFilter saved in Settings applies to every formula without its own filter: on a range without those columns the formula shows the error above.

# prefix is reserved for index syntax and is not treated as a column-name filter token.

colFilter applies to all expressions in query. It does not create separate groups per expression.