STATS_ONE_SAMPLE – One-sample t-test in Google Sheets™

What it does

One-sample t-test against mu0.

Performs a one-sample t-test to compare the sample mean against a hypothesized value (μ₀).

The confidence interval is the interval of the difference mean − μ₀, as in SPSS; with μ₀ = 0 it is the interval of the mean. One-sided alternatives give one infinite bound.

With more than one column in colName, the first is tested and the others are dropped.

Syntax

=STATS_ONE_SAMPLE(columns, colName, mu0, ciLevel, alternative)

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.

colName — required

Name of the numeric column to test.

Examples:

score
mood
motivation

mu0 — optional, default 0

Hypothesized population mean (μ₀): any number, including decimals and negative numbers (5.5, -3, "5" written as text).

If omitted, the value from Settings (0 by default). Not a number is an error: mu0 must be a number.

Should be any number

Examples:

0
5.5
-3

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"

Examples

— the sample data (50 rows) all examples use
— =STATS_ONE_SAMPLE(Data!A1:T51, "score", 5, 0.95,"two-sided")
— =STATS_ONE_SAMPLE(Data!A1:T51, "score")
— =STATS_ONE_SAMPLE(Data!A1:T51, "x", 5)
— =STATS_ONE_SAMPLE(Data!A1:T51, "x", 5, 0.99, "greater")

What the result contains

A flattened 2D report including:

Title ("One-sample T-Test (two-sided) for x" — the hypothesis is named once);
t, df, p, mean, meanDelta (mean − μ₀), sd, se, n;
Confidence interval of the difference (Level, Lower, Upper) as numbers.

How to read the result

Start with p: below 0.05 — the mean differs from the test value μ₀.

meanDelta says by how much, in the units of the data; the interval says how precisely.

In a report: "The mean score (M = 5.30, SD = 3.07) did not differ significantly from 5, t(49) = 0.69, p = .492."

What each number means

t — t of the test

Beyond 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 test

Written in brackets after t: t(49) = 0.69.

p — p of the test

Below 0.05 — the mean differs from μ₀ beyond chance. In a report: "The mean (M = 5.30, SD = 3.07) did not differ significantly from 5, t(49) = 0.69, p = .492."

mean — Mean

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

meanDelta — Mean difference

How far the mean is from the value you compare it with, in the units of the data: 0.3 means the mean is 0.3 points above μ₀.

sd — Standard deviation

How far the values typically lie from the mean. In a report: M = 5.30, SD = 3.07.

se — Standard error of the mean

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

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

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.

How it is computed

The first selected column is tested; its valid values are used (empty cells and cells of another type left out).

t = (x̄ − μ₀) / (sd / √n) with n − 1 degrees of freedom; sd is the sample one (n − 1), whatever pop says, as SPSS T-TEST.

p is two-sided or one-sided by alternative; the confidence interval is that of the difference x̄ − μ₀, as SPSS.

A column without spread gives t = ±∞ (or 0 when the mean equals μ₀) instead of an error.

Errors

If the specified column does not exist: "Column nope does not exist"; fewer than 2 numeric values — a single-cell message.

A μ₀ that is not a number: "mu0 must be a number".

About STATS_ONE_SAMPLE

STATS_ONE_SAMPLE compares the mean of one column with a test value μ₀ — for example, whether the average satisfaction score differs from the midpoint of the scale. μ₀ is 0 by default; any number can be given, including decimals and negative numbers. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

The report gives t, the degrees of freedom (df), p — the probability of a difference at least this large if the true mean were μ₀ — the mean, the difference mean − μ₀, the standard deviation, the standard error and n. t uses the sample standard deviation (n − 1), as SPSS T-TEST, and the confidence interval is that of the difference mean − μ₀, as in SPSS; with μ₀ = 0 it is the interval of the mean.

The test is two-sided by default; "greater" or "less" makes it one-sided, and the interval then has one open end. Only the valid values of the column are used: empty cells and cells of another type are left out. Every number of the report is explained on this page in plain words.