STATS_INDEPENDENT – Independent-samples t-test with full report in Google Sheets™

What it does

Independent-samples t-test for two groups: the two selected columns are the groups.

With more columns than the test needs, the first two are used; the others are dropped before anything is computed, so the title, the homogeneity test and the Student/Welch choice are about these two only.

Student or Welch test, chosen by welch (auto by default); the automatic choice uses the homogeneity test and the thresholds in Settings.

The homogeneity test uses absolute deviations from the group center, as it is defined; the automatic Student/Welch choice follows it, so t, df, p and the interval can differ from older versions.

Syntax

=STATS_INDEPENDENT(columns, colFilter, welch, ciLevel, alternative, homogeneity)

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
alternative — optional, default "two-sided"

Alternative hypothesis for two-sample/one-sample tests and correlations: "two-sided", "greater" or "less"; 1 and -1 (number or text) mean greater and less.

Case and spaces around do not matter ("Greater", " less "). Another value is an error: alternative must be two-sided, greater or less (or 1, -1), got "grater". Empty — the default from Settings.

A one-sided hypothesis gives a one-sided interval: [lower, ∞) for greater, (−∞, upper] for less (for correlations [lower, 1] and [−1, upper]).

Allowed values: "two-sided", "greater", "less"

Examples:

  • "two-sided"
  • "greater"
  • "less"
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"

Examples

  • — the sample data (50 rows) all examples use
  • — =STATS_INDEPENDENT(Data!A1:T51, "y,x1", "welch",0.95)
  • — =STATS_INDEPENDENT(Data!A1:T51, "x,x1")
  • — =STATS_INDEPENDENT(Data!A1:T51, "x,x1", "welch")
  • — =STATS_INDEPENDENT(Data!A1:T51, "q1,q2", "student", 0.95, "greater")
  • — =STATS_INDEPENDENT(Data!A1:T51, "x,x2", "auto", 0.95, "two-sided", "levene")

What the result contains

Flattened 2D 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 two groups differ.

Look at the means in Groups for the direction and at the interval for the size of the difference.

Check which test was run (welch in Model summary, or the title): Welch when the variances or sizes differ.

The effect size, Cohen's d, is not in the report: d = (M₁ − M₂) / s_pooled, s_pooled = √(((n₁ − 1)Sd₁² + (n₂ − 1)Sd₂²) / (n₁ + n₂ − 2)), from the Groups table; about 0.2 small, 0.5 medium, 0.8 large.

In a report: "Group y (M = 6.06, SD = 2.53) scored higher than x1 (M = 3.04, SD = 1.29), Welch's t(72.9) = 7.50, p < .001, d = 1.50."

What each number means

Model summary

se — Standard error of the difference

The precision of the difference: t is the difference divided by it, and the interval is built around it. Rarely reported on its own.

welch — Test variant

1 — Welch's t test was used, 0 — Student's. Student's test assumes that both groups scatter equally; Welch's does not. The add-on chooses for you.

In a report: "Welch's t test was used because the variances differed", or simply "an independent-samples t test".

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.

alternative — Alternative hypothesis

Leave it two-sided unless you decided on the direction before looking at the data; a one-sided test halves p in the expected direction.

Confidence interval

Level — Confidence level

0.95 means that in 95 samples of 100 an interval built this way would contain the true value.

Lower — Lower bound of the difference

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

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

Test results

t — t of the test

Beyond about ±2 the difference is usually significant; read p. In a report t goes with df: t(98) = 2.35, p = .021.

df — Degrees of freedom of the t test

Written in brackets after t: t(98) = 0.28, or after Welch t(72.9) = 7.50 — a fractional df is normal there.

p — p of the test

Below 0.05 — the groups differ beyond chance. In a report: "Group y scored higher than x1, Welch's t(72.9) = 7.50, p < .001."

F — F = t²

Shown so the result can be compared with an ANOVA of the two groups; read t and p instead.

cohenD — Cohen's d

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

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

Groups

Groups — Groups

Report the means and standard deviations of both groups next to t and p.

Variable — Variable

Read the numbers along this row or column for that variable.

n — Number of values

How many observations the result rests on. Compare it with the size of your sample: empty cells are left out.

Mean — Mean of the group

Compare the two means: their difference is what the test checks. In a report: M = 6.06 against M = 3.04.

Sd — Standard deviation of the group

How much the values of the group scatter. Very different Sd in the two groups is what Welch's test is for.

How it is computed

The first two selected columns are the two groups; each keeps its own valid values, so the groups may have different sizes.

Levene's test of equal variances (on distances from the median by default — the Brown–Forsythe variant; from the mean, as SPSS T-TEST prints it, with homogeneity "levene"); then, with welch "auto", Student's or Welch's test by the rule in Model summary. SPSS prints both tests as two rows; ALS Stats reports the one chosen.

Student: t = (x̄₁ − x̄₂) / √(s²p (1/n₁ + 1/n₂)) with n₁ + n₂ − 2 df. Welch: t = (x̄₁ − x̄₂) / √(s²₁/n₁ + s²₂/n₂) with the Welch–Satterthwaite df. Sample variances (n − 1), as SPSS T-TEST.

p is two-sided or one-sided by alternative; the confidence interval is that of x̄₁ − x̄₂ with the se and df of the chosen test.

Errors

A column that does not exist: "Column nope does not exist".

Fewer than two groups: "Independent T Test requires at least 2 groups".

A text column without value labels, an unknown welch/homogeneity/alternative value or ciLevel outside 0…1 → a single-cell message naming it.

Regex is case-sensitive and has no flags.

About STATS_INDEPENDENT

STATS_INDEPENDENT compares the means of two independent groups with a t-test. Each of the two selected columns is one group, for example the scores of men and of women, and each keeps its own answers, so the groups may differ in size. 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 Student's t-test when the groups scatter alike and are of similar size, and Welch's test otherwise; the Model summary shows the numbers behind the choice. Levene's test, which checks whether the variances differ, is based on the median by default (the Brown–Forsythe variant) or on the mean, as SPSS T-TEST prints it. SPSS prints both t-tests; ALS Stats reports the one chosen.

The report gives t, degrees of freedom, p (two-sided, or one-sided with alternative), the confidence interval of the difference of means and Cohen's d, the size of the difference in standard deviations, together with the mean, SD and n of each group. Every number of the report is explained on this page in plain words.