STATS_ANOVA – One-way ANOVA and Welch ANOVA in Google Sheets™

What it does

One-way ANOVA: each selected column is a group. Classic (Student) or Welch ANOVA, chosen by welch (auto by default), with homogeneity of variances and post-hoc comparisons.

Parameter ciLevel, used only with posthoc.

posthoc "auto" is Tukey HSD after a classic ANOVA and Games–Howell after a Welch ANOVA; "bonferroni" and "scheffe" can be chosen, "none" or FALSE turns the table off.

The post-hoc table appears only when the ANOVA is significant (p < 0.05, whatever ciLevel is); ciLevel sets its confidence intervals.

In the post-hoc table "p" is the pairwise test without correction and "Adjusted p" is the p of the chosen method.

Post-hoc tests take time: with many groups the formula can approach the 30-second limit of Sheets custom functions; turn posthoc off when it is not needed.

Syntax

=STATS_ANOVA(columns, colFilter, welch, ciLevel, homogeneity, posthoc)

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 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.

colFilter — optional, default "*"

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.

welch — optional, default "auto"

Test variant for the independent t-test and ANOVA: "welch", "student" or "auto"; TRUE or 1 means welch, FALSE or 0 student (as a checkbox, a number or text).

Allowed values: "welch", "student", "auto", TRUE ("welch"), FALSE ("student")

Examples:

  • "welch"
  • "student"
  • "auto"
  • "varRatioMaxStudent=3" — auto with a looser variance-ratio threshold for this formula

"auto" chooses Student when the groups look alike: Levene p ≥ alphaHomogeneity, variance ratio ≤ varRatioMaxStudent, size ratio ≤ balanceMaxStudent and every group at least smallNForStudent; otherwise Welch. The thresholds are set in Settings.

Thresholds for one formula only: write them instead of the mode, "varRatioMaxStudent=3,smallNForStudent=10" — auto with these thresholds.

Case and spaces around do not matter ("Welch", " student "). An unknown mode or threshold name is an error that lists the variants, for example: welch must be auto, student or welch, got "studnt".

ciLevel — optional, default 0.95

Confidence level for intervals in tests/bootstraps: a number strictly between 0 and 1 (0.95, not 95). A number written as text ("0.9") is accepted.

Another value is an error: ciLevel must be between 0 and 1 (e.g. 0.95), got 95. Empty — the level from Settings.

Should be a number between 0 and 1

Examples:

  • 0.9
  • 0.95
  • 0.8
homogeneity — optional, default "brown-forsythe"

Variance-homogeneity test: "brown-forsythe" (Levene based on the median, the default) or "levene" (based on the mean, as SPSS T-TEST). Also accepted: "median", "mean", TRUE (= brown-forsythe), FALSE (= levene).

The table is titled "Levene (based on median)" or "Levene (based on mean)".

Case and spaces around do not matter ("Levene"). Another value is an error: homogeneity must be brown-forsythe or levene (or true, false, median, mean), got "levine".

Allowed values: "brown-forsythe", "levene"

Examples:

  • "brown-forsythe"
  • "levene"
posthoc — optional, default "auto"

Post-hoc comparisons after ANOVA: "auto", "tukey", "games-howell", "bonferroni", "scheffe" or "none".

Empty — the default from Settings ("auto"). "auto" — Tukey HSD after a classic ANOVA, Games–Howell after a Welch ANOVA.

"none", FALSE or 0 — no post-hoc table; TRUE or 1 — "auto" (as a checkbox, a number or text).

Case and common spellings do not matter ("Bonferroni", "bonf", "hsd", "gh", "games howell", "sheffe"); an unknown value is an error that lists the variants.

The table is built only when the ANOVA is significant (p < 0.05) — a rule of ALS Stats: SPSS ONEWAY prints a requested posthoc table whatever the ANOVA shows.

With two groups no post-hoc table is built (it would repeat the ANOVA): when the ANOVA is significant, the report says "Posthoc tests are not performed: fewer than three groups" instead.

Allowed values: "auto", "tukey", "games-howell", "bonferroni", "scheffe", "", "none" or FALSE (no table), TRUE ("auto")

Examples:

  • "auto" — Tukey after a classic ANOVA, Games–Howell after Welch
  • "bonferroni" — pairwise t-tests with the Bonferroni correction
  • "none" — no post-hoc table

Examples

  • — the sample data (50 rows) all examples use
  • — classic one-way ANOVA =STATS_ANOVA(Data!A1:T51, "x1,x2")
  • — Welch variant =STATS_ANOVA(Data!A1:T51, "x1,x2", TRUE)
  • — regex selector: every column whose name starts with x =STATS_ANOVA(Data!A1:T51, "/^x/")
  • — Bonferroni post-hoc =STATS_ANOVA(Data!A1:T51, "x,x1,x2", "auto", 0.95, "", "bonferroni")
  • — no post-hoc table =STATS_ANOVA(Data!A1:T51, "x,x1,x2", "auto", 0.95, "", "none")
  • — Error example (returns single-cell message) =STATS_ANOVA(Data!A1:T51, "x1")

What the result contains

A report that spills below the formula cell:

How to read the result

Each group must be its own column. If the scores are in one column and the group (gender, class) in another, first split them with STATS_SPLIT_COLS — one column per group — and select those columns here.

Start with p in Test results. Below 0.05 — the group means are not all equal; at 0.05 or above — the data show no difference.

Check which test was run in Model summary (student or welch): the numbers next to it explain the choice, and the Levene table below shows the p of equal variances.

If p is below 0.05, see how large the difference is: η² or ω² in Test metrics (0.01 small, 0.06 medium, 0.14 large).

Then find which pairs of groups differ in the posthoc table (posthoc — the pairwise comparisons made after the ANOVA): a pair with Adjusted p below 0.05, and an interval that does not contain 0.

In a report, write F with both degrees of freedom, p and the effect size, for example: F(2, 147) = 4.12, p = .018, η² = .05. After Welch, say so: Welch's F(2, 96.4) = 4.30, p = .016.

What each number means

Model summary

welch — Test variant

Which 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 ratio

How 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 ratio

How 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 groups

How many groups are compared. ANOVA needs at least 2; the posthoc pairs need 3 or more.

Test results

F — F statistic

F 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 groups

The first number in brackets after F: the number of groups minus one. With 3 groups it is 2.

dfWithin — Degrees of freedom within groups

The 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 ANOVA

Do 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 groups

With 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 F

The same idea applied to spread: do the groups scatter differently? Read its p, not F.

df1 — Levene degrees of freedom 1

The first number in brackets after F: F(df1, df2). You report it, you do not interpret it.

df2 — Levene degrees of freedom 2

The second number in brackets after F: F(df1, df2). You report it, you do not interpret it.

p — p-value of the Levene test

A 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 groups

An 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 groups

The other ingredient of F: how much individuals differ within their own group — the part that the groups do not explain.

msb — Mean square between groups

ssb 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 groups

ssw 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 groups

Which two groups this row compares. The difference in the row is the first group minus the second.

Mean difference — Mean difference

How 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 difference

How 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 pair

Degrees 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 pair

The same for this pair: positive — the first group of the pair has the higher mean, negative — the second.

p — Unadjusted p of the pair

The 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 pair

The 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 interval

Together 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 interval

Together 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 intervals

The confidence level of the intervals above and the ANOVA they follow: student — the classic test, welch — Welch's test.

How it is computed

Each selected column is a group. Empty cells and cells that do not match the column type are left out of that group only; the groups may have different sizes.

First the Levene test of equal variances (on distances from the group median by default, from the mean with homogeneity "levene"). Then, with welch "auto", the classic test or Welch's test by the rule in Model summary.

The classic one-way ANOVA: F = msb / msw with k − 1 and N − k degrees of freedom. Welch's ANOVA (Welch, 1951): group means weighted by nᵢ / s²ᵢ, fractional second degrees of freedom. p is the upper tail of the F distribution.

Sums of squares and η² are always the classic ones, as SPSS ONEWAY prints them; ω² (which SPSS does not print) is computed from the same sums. SPSS shows the classic ANOVA and Welch's test in two tables; ALS Stats reports the one chosen.

Posthoc: Tukey HSD (Tukey–Kramer for unequal groups) and Games–Howell use the studentized range distribution; Bonferroni multiplies the pairwise p by the number of pairs; Scheffé uses the F distribution (Bonferroni and Scheffé as in SPSS).

Errors

If validation fails, the formula returns a single-cell message instead of the report. Typical situations:

With two groups there is no post-hoc table; when the ANOVA is significant, the report says "Posthoc tests are not performed: fewer than three groups" instead.

About STATS_ANOVA

STATS_ANOVA compares the means of two or more groups. Each selected column is one group — for example, the satisfaction scores of three departments. If the scores are in one column and the group in another, split them first with STATS_SPLIT_COLS. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

You do not have to choose the test. By default the add-on runs the classic (Student) ANOVA when the groups scatter alike and are of similar size, and Welch's ANOVA otherwise; the Model summary shows the numbers behind the choice, and the Levene table shows whether the variances differ. Sums of squares and η² follow the definitions of SPSS ONEWAY; ω², a less biased effect size, is added next to it.

When the ANOVA is significant (p < 0.05), a post-hoc table shows which pairs of groups differ: Tukey after the classic test, Games–Howell after Welch, or Bonferroni and Scheffé when you ask for them. Every number of the report is explained on this page in plain words, together with the line to write in your report, for example: F(2, 147) = 4.12, p = .018, η² = .05.