STATS_ANALYZE – several analyses in one report in Google Sheets™
What it does
Runs several analyses on one range and returns one report.
Each analysis is written like its STATS function without "STATS_" and without the range, with the same parameters in the same order: PAIRED('before,after', 0.99, 'greater'). The old syntax pearson(x1,x2|0.99) still works; see tests.
Parameters are applied as written: ci(x|0.9) and CI('x', 0.9) give a 90% interval.
Syntax
=STATS_ANALYZE(columns, tests)Parameters
columns — required
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 columnsA1:C200— use 3 columnsA:F— use 6 columnsA1: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.
tests — required
One text with several analyses, or a range of cells (one or more calls per cell). Calls follow each other with spaces, ";" or new lines.
New syntax (recommended): the STATS function without "STATS_" and without the range, parameters in the same order — PAIRED('before,after', 0.99, 'greater'). The name in any case; text in single quotes (inside a formula double quotes would have to be doubled); commas, "|" and ")" inside quotes do not split; an empty parameter is the default: PAIRED('a,b', , 'greater').
New-syntax names: ALPHA, PAIRED, INDEPENDENT, ANOVA, MANN_WHITNEY, WILCOXON, KRUSKAL, ONE_SAMPLE, LINEAR, LOGISTIC, BOOTSTRAP, SPEARMAN, KENDALL, PEARSON, PARTIAL, DESCRIPTIVE, CI, NORMALITY, FREQUENCIES.
Old syntax (still supported): "<test>(<part1>|<part2>|...)". Parts go in the order of the STATS function: descriptive(mean,std,n|x1,x2) — metrics first, as in STATS_DESCRIPTIVE; paired(before,after|0.99|greater). A part may be key=value: paired(a,b|alternative=greater). Regressions: Y, then steps through "|", options as key=value: linear(y|x1|x1,x2|lambda=0.1).
In the old syntax a call ends at the first ")": a regular expression with brackets or "|" needs the new syntax — PEARSON('/(score|age)/'). alpha+(...) adds the "if item deleted" table, alpha(...|false) leaves it out.
Crosstabs, chi-square and Cramer's V exist only in the old syntax: crosstabs(gender,group), chi2(gender,group), cramer(gender,group).
Supported tests (old-syntax names):
● frequencies ● descriptive ● normality ● ci ● paired ● independent ● anova ● mannwhitney ● wilcoxon ● kruskal ● kruskalwallis ● onesample ● alpha+ ● alpha ● spearman ● kendall ● pearson ● partial ● linear ● logistic ● bootstrappairs ● bootstrapresidual ● bootstrap ● crosstabs ● chi2 ● chisquare ● cramer
Examples:
PAIRED('before,after', 0.99, 'greater') ANOVA('x1,x2,x')— new syntaxpearson(x1,x2) anova(x1,x2)— old syntaxdescriptive(mean,std,n|x1,x2) spearman(x1,x2)— old syntax: metrics first, as in STATS_DESCRIPTIVE
Examples
- — the sample data (50 rows) all examples use
- — new syntax, 3 reports
=STATS_ANALYZE(Data!A1:T51, "PAIRED('before,after', 0.99, 'greater') ANOVA('x1,x2,x') DESCRIPTIVE('mean,std,n', 'x1,x2')") - — old syntax, 3 reports
=STATS_ANALYZE(Data!A1:T51, "pearson(x1,x2)alpha(/^q/)anova(x1,x2)") - — old syntax: regression steps, one-sample test against 15
=STATS_ANALYZE(Data!A1:T51, "linear(y|x1|x2) oneSample(x1|15)")
What the result contains
A single report that spills below the formula cell:
- the reports of the analyses, in the order they are written, each as its STATS function shows it;
- one line per failed analysis, after the reports. When no analysis succeeds, only these lines are shown.
How to read the result
Read each report as the report of its function: the title says which test it is, and the help of that function explains its numbers.
Check the lines after the last report: a test with an error is not in the list above.
What each number means
t— t of the testBeyond about ±2 the difference is usually significant; read p. In a report t goes with df: t(49) = 2.35, p = .023.
df— Degrees of freedom of the t testWritten in brackets after t: t(49) = 0.69.
p— p of the testBelow 0.05 — the two measurements differ beyond chance. In a report: "Scores did not change significantly, t(49) = 0.61, p = .547."
meanDelta— Mean differenceHow much the first measurement is higher than the second on average: 0.16 means "before" is 0.16 points above "after". The sign follows the order of the columns in the formula.
sdDelta— Standard deviation of the differencesHow much the change differs from person to person. A small sdDelta next to a clear meanDelta means nearly everyone changed the same way.
se— Standard error of the mean differenceThe precision of the difference: t is the difference divided by it, and the interval is built around it. Rarely reported on its own.
n— Number of valuesA row with one of the two answers missing is left out — compare n with your sample.
cohenD— Cohen's dHow big the difference is, not only whether it exists: p says "not chance", d says "how much". Rough guide (Cohen): 0.2 small, 0.5 medium, 0.8 large; the sign follows the order of the columns.
APA asks for it next to the test: t(98) = 2.10, p = .038, d = 0.42.
Metric / Column— Metric / ColumnRead a row across to compare one metric between the columns.
mean— MeanThe typical value when the data are roughly symmetric. One extreme value pulls it strongly, so compare it with the median: if they differ a lot, the data are skewed.
In a report next to the standard deviation: M = 3.04, SD = 1.29.
std— Standard deviationHow far the values typically lie from the mean, in the units of the data: with a mean of 3 and std 1.3 on a 1–5 scale most answers are between about 2 and 4.
In a report next to the mean: M = 3.04, SD = 1.29.
Calculated for Sample.— How the metrics were computed"Calculated for Sample." — std, variance and the metrics built on them use the sample formulas (n − 1); "Calculated for Population." — pop = TRUE in Settings (n).
"Some calculations made with epsilon …" — a zero or tiny value was raised to epsilon in geometricMean or harmonicMean.
Text columns left out of the table are named at the end of the line.
covariance— CovarianceIts size depends on the units of the columns, so it is hard to read on its own; r is the same relation on a scale from −1 to 1.
df— Degrees of freedom of the correlationWritten in brackets after r in a report: r(48) = .15.
r— Pearson correlation rHow closely two things go together: 0 — no linear relation (a curved one can still exist), 1 — they rise together perfectly along a straight line, −1 — one falls exactly as the other rises. A common guide (Cohen): 0.1 small, 0.3 medium, 0.5 large.
Correlation is not causation: r says that the two go together, not that one causes the other.
In a report: r(48) = .15, p = .293 — the number in brackets is df.
p— p of the correlationBelow 0.05 — the relation is unlikely to be chance. With many rows even a tiny r becomes significant, so read r itself for the strength.
t— t of the correlationA step on the way to p; for a correlation it is not usually reported.
Alpha— Cronbach’s alphaAt most 1, and it can be negative. 0.7 is the usual minimum for a research scale, 0.8 good, 0.9 excellent (above 0.95 the items may repeat each other). A low or negative alpha means the items do not hang together: check the coding of reversed items, and whether the items measure one thing at all.
In a report: "The scale was reliable, α = .84 (7 items)."
se— Standard error of the meanThe precision of the difference: t is the difference divided by it, and the interval is built around it. Rarely reported on its own.
Confidence interval
Level— Confidence level0.95 means that in 95 samples of 100 an interval built this way would contain the true value.
Lower— Lower bound of the differenceTogether with the other bound: a range of plausible values for the true difference. An interval that does not contain 0 matches p below 0.05 for a 95% interval; its width shows how precisely the difference is known.
Upper— Upper bound of the differenceTogether with the other bound: a range of plausible values for the true difference. An interval that does not contain 0 matches p below 0.05 for a 95% interval; its width shows how precisely the difference is known.
Lower— Lower bound for rTogether with the other bound: a range of plausible values for the true correlation. If the interval contains 0, the relation may be nil; a wide interval (−0.13 to 0.41) means the sample is too small to tell the strength.
Upper— Upper bound for rTogether with the other bound: a range of plausible values for the true correlation. If the interval contains 0, the relation may be nil; a wide interval (−0.13 to 0.41) means the sample is too small to tell the strength.
Model summary
welch— Test variantWhich version of ANOVA was used. The classic ANOVA assumes that all groups scatter equally; when they do not, or when the groups are of very different sizes, its p can mislead, and Welch's version gives a fair one.
The add-on chooses for you, unless the formula says "student" or "welch" — then the report shows what you asked for, even if the numbers next to it argue against it. In a report say which one was used: "Because the variances differed, Welch's ANOVA was used."
varRatio— Variance ratioHow unequal the scatter of the groups is: 3 means that the values of one group vary three times as much as those of another.
Above 2 the add-on prefers Welch's test, which does not need equal scatter.
balance— Group size ratioHow unequal the group sizes are: 2 means the largest group is twice the smallest.
Unequal groups together with unequal scatter make the classic test unreliable, so above 1.5 the add-on prefers Welch.
k— Number of groupsHow many groups are compared. ANOVA needs at least 2; the posthoc pairs need 3 or more.
Predictors— PredictorsThe headings of this table: read each column as one model.
R— Multiple correlation RFrom 0 to 1. It is rarely reported: R Square, its square, is easier to read.
R Square— R²R² = 0.25 means the predictors account for a quarter of the differences in Y. A common guide (Cohen): 0.02 small, 0.13 medium, 0.26 large.
In a hierarchical model the rise of R² from one step to the next is what the added predictors explain on top of the earlier ones; the ANOVA table of each step gives it as R Square Change with its F Change test.
In a report: R² = .25, F(2, 47) = 7.83, p = .001.
Adjusted R Square— Adjusted R²The fair version of R² for comparing models with different numbers of predictors. It can fall below 0 when the model explains nothing at all.
Std. Error of the Estimate— Standard error of the estimateHow precise the predictions are: with 2.5, a prediction is typically off by about 2.5 points of Y.
Sample size— Sample sizeCompare it with your sample: every row with a gap in any of the columns is dropped.
Calculated with lambda 0— Ridge penalty used0 is the ordinary regression — the one you usually want. Ignore this line unless you set lambda yourself.
Test results
F— F statisticF compares how much the groups differ with how much individuals differ inside each group. F near 1 — the groups differ no more than people do anyway; the larger F, the more the group matters.
F on its own is hard to read — look at the p next to it. In a report F goes with its two degrees of freedom: F(2, 147) = 4.12.
dfBetween— Degrees of freedom between groupsThe first number in brackets after F: the number of groups minus one. With 3 groups it is 2.
dfWithin— Degrees of freedom within groupsThe second number in F(…, …): roughly the number of values minus the number of groups.
After Welch it is fractional, for example 96.4 — that is normal; report it rounded to one decimal: Welch's F(2, 96.4) = 4.30.
p— p-value of the ANOVADo the groups differ at all? Below 0.05 — yes, at least one group stands out; the ANOVA does not say which one, the posthoc table does.
At 0.05 or above, write that no significant differences between the groups were found. That is a valid result too, not a failure, and then the pairs are not compared.
In a report: "Job involvement differed between the age groups, F(2, 147) = 4.12, p = .018, η² = .05."
Posthoc tests are not performed: fewer than three groups— No posthoc table for two groupsWith two groups the only pair is the ANOVA itself, so a posthoc table would repeat it. The line appears only where the table would: when the ANOVA is significant (p < 0.05) and posthoc is not turned off. The difference is in the means of the two groups.
Test of equal variances
F— Levene FThe same idea applied to spread: do the groups scatter differently? Read its p, not F.
df1— Levene degrees of freedom 1The first number in brackets after F: F(df1, df2). You report it, you do not interpret it.
df2— Levene degrees of freedom 2The second number in brackets after F: F(df1, df2). You report it, you do not interpret it.
p— p-value of the Levene testA check before the main test: do the groups scatter equally? Below 0.05 — they do not, and the add-on switches to Welch's test (ANOVA or t test), which is built for that.
It is rarely reported on its own; if needed: "Levene's test showed unequal variances (p = .03), so Welch's test was used."
Test metrics
ssb— Sum of squares between groupsAn ingredient of F: how much the group averages differ from the overall average. You rarely report it; it is shown for checking and for SPSS-style tables.
ssw— Sum of squares within groupsThe other ingredient of F: how much individuals differ within their own group — the part that the groups do not explain.
msb— Mean square between groupsssb per degree of freedom — the average variation between the groups. In the classic ANOVA F = msb / msw; Welch's ANOVA reports its own weighted F, so there the ratio differs from F.
msw— Mean square within groupsssw per degree of freedom — the average variation inside the groups. In the classic ANOVA F = msb / msw; after Welch the F is computed differently.
eta2— Eta squared (η²)How much the grouping explains. η² = 0.10 means that 10% of the differences between people go with the group they are in.
p says whether there is an effect, η² says how big it is: 0.01 small, 0.06 medium, 0.14 large.
Report it next to p: F(2, 147) = 4.12, p = .018, η² = .05 — close to a medium effect.
omega2— Omega squared (ω²)The same share as η², corrected for the size of the sample, so a little more modest and more honest.
If ω² is close to 0 while η² is not, the effect is probably mostly noise. Many journals prefer ω² for this reason.
Posthoc comparisons
Groups— Pair of groupsWhich two groups this row compares. The difference in the row is the first group minus the second.
Mean difference— Mean differenceHow far apart the two groups are, in the units of your data: 0.54 means the first group scored 0.54 points higher on average; a negative value — the second group of the pair is higher.
This is the number readers understand best: name the groups in the report — "Third-year students scored 2.18 points higher than first-year students (Tukey, p < .001)."
Standard error of difference— Standard error of the differenceHow precise the difference is: a small standard error means another sample would give a similar difference. It is used for t and the interval and is rarely reported on its own.
df— Degrees of freedom of the pairDegrees of freedom — roughly, how much data is left to estimate the spread once the means are estimated. You do not interpret them; you report them next to the statistic, for example t(45) = 2.10.
t— t statistic of the pairThe same for this pair: positive — the first group of the pair has the higher mean, negative — the second.
p— Unadjusted p of the pairThe p of this pair as if it were the only comparison. Use Adjusted p instead: with 6 pairs some raw p values fall below 0.05 by luck alone.
Adjusted p— Adjusted p of the pairThe p of the pair after the correction for making many comparisons at once — this is the one to use. Below 0.05 — these two groups differ significantly. It can be exactly 1: the correction left no difference at all — write p > .99.
In a report: "Group 2 scored higher than group 3 (Tukey, p = .021)."
CI0.95 lower— Lower bound of the intervalTogether with the upper bound: a range of plausible values for the true difference. If the whole range is on one side of zero, the groups differ; if it crosses zero, the difference may be nil.
The width matters too: 0.1 to 1.0 means "a real difference, but anywhere from tiny to large".
CI0.95 upper— Upper bound of the intervalTogether with the lower bound: a range of plausible values for the true difference. If the whole range is on one side of zero, the groups differ; if it crosses zero, the difference may be nil.
The width matters too: 0.1 to 1.0 means "a real difference, but anywhere from tiny to large".
Confidence Interval 0.95 (student)— Level of the posthoc intervalsThe confidence level of the intervals above and the ANOVA they follow: student — the classic test, welch — Welch's test.
ANOVA of the model
Source— Source of variationRead each row as one part of the differences in Y.
SS— Sum of squaresThe ingredient of R² (Regression SS / Total SS) and of F; rarely reported on its own.
df— Degrees of freedom of the model and the errorReported in brackets after F: F(2, 47) — the Regression df first, then the Residual df.
MS— Mean squareF is MS Regression divided by MS Residual.
F— F of the modelRead its Sig. In a report: F(2, 47) = 7.83, p = .001.
Sig.— p of the modelBelow 0.05 — the predictors together explain Y better than its mean alone. At 0.05 or above — the model as a whole does not help significantly.
R Square Change— R² changeΔR² = 0.06 means the predictors added at this step explain 6% of Y on top of the earlier ones. Read it with Sig. F Change next to it.
In a report: ΔR² = .06, F(1, 7) = 15.39, p = .006.
F Change— F changeRead its Sig. F Change; F itself goes into the report with df1 and df2 next to it: F(1, 7) = 15.39.
df1— Degrees of freedom 1 of the F changeThe first number in brackets after F: F(df1, df2). You report it, you do not interpret it.
df2— Degrees of freedom 2 of the F changeThe second number in brackets after F: F(df1, df2). You report it, you do not interpret it.
Sig. F Change— p of the F changeBelow 0.05 — the predictors added at this step help significantly beyond the earlier ones; at 0.05 or above — the step adds nothing reliable, and the simpler model is enough.
Regression— RegressionIts F and Sig. are the test of the whole model.
Residual— ResidualIts MS is the variance of the prediction errors; its square root is the Std. Error of the Estimate.
Total— TotalThe same number for every model on the same rows: only its split between Regression and Residual changes.
Coefficients
Variable— VariableRead the numbers along this row or column for that variable.
Intercept— InterceptRarely interpreted — a predictor value of 0 is often outside the data (age 0). It is needed to compute predictions.
B— Coefficient BB = 0.5 for hours: one more hour, half a point more, all else equal. The sign gives the direction; compare predictors in different units by Beta, not by B.
StdError— Standard error of BLarge next to B — the coefficient is imprecise. It is used for the test and the interval and is rarely reported on its own. SPSS calls it Std. Error (REGRESSION) or S.E. (LOGISTIC REGRESSION).
Beta— Standardized coefficient βBeta puts predictors measured in different units on one scale: the larger |Beta|, the stronger the predictor within this model.
t— t of the coefficientBeyond about ±2 the coefficient usually becomes significant; read p next to it.
p— p of the coefficientBelow 0.05 — this predictor adds to the prediction with the other predictors held fixed.
In a report: "Study hours predicted the score, B = 0.50, β = .42, t(47) = 3.10, p = .003."
VIF— Variance inflation factor1 — no overlap with the other predictors. Above 5 (some say 10) the predictors overlap so much that their separate B are unreliable: drop one of them or set lambda above 0.
How it is computed
Each test of the list is run as its own STATS function on the same range, in the written order, and the reports follow one another.
Every report is computed exactly as by its function: the numbers of a test in ANALYZE and of the same STATS formula are the same — except bootstrap intervals, which are random and change on every calculation.
A test that cannot run leaves a line with the reason after the reports; the other tests are still computed.
Errors
A failed analysis does not stop the others: its error is one line after the reports, for example
"Column nope does not exist", "Method foo not supported",
"paired: too many parameters (4), expected colFilter, ciLevel, alternative",
"pearson: a regular expression with ( ) or | needs the new syntax, e.g. PEARSON('/(score|age)/')".
About STATS_ANALYZE
STATS_ANALYZE runs several analyses on one data range and returns their reports one after another below the formula cell, in the order you wrote them. It suits a results chapter where the same survey data needs descriptive statistics, a t-test and a correlation at once. The whole report recalculates by itself when new responses arrive from Google Forms.
Each analysis is written like its STATS function without "STATS_" and without the range, with the same parameters in the same order: PAIRED('before,after', 0.99, 'greater') runs the paired-samples t-test of before and after with a 99% confidence level and the one-sided alternative greater. Text goes in single quotes. The older short form, such as pearson(x1,x2), still works; crosstabs, chi-square and Cramer's V are written only in that form, for example chi2(gender,group).
Every report is computed exactly as by its own function, so the numbers are the same as in the separate STATS formula, except bootstrap intervals, which are random and change on every calculation. An analysis that cannot run leaves one line with the reason after the reports, for example "Column nope does not exist"; the other analyses are still computed. Every number of each report is explained on this page in plain words.