STATS_WILCOXON – Wilcoxon signed-rank test in Google Sheets™

What it does

Wilcoxon signed-rank test for two related measurements of the same rows (before / after) — the rank replacement for STATS_PAIRED when the differences are not normal.

The difference is the second column minus the first, as SPSS reads a pair, and the rank rows say it in words: "Negative Ranks (after < before)". STATS_PAIRED takes the first minus the second — the direction is in the table itself.

Pairs are rows: a missing value in either column drops the row. Equal pairs go to Ties and out of the ranking; Total counts every pair. The first two columns of colFilter are used, further columns are dropped.

Two-sided: Z is built on the smaller rank sum and is never positive, exactly as SPSS prints it; the direction is in the Ranks table. One-sided (which SPSS does not print): Z keeps the sign of the effect so that it agrees with its p. |Z| and the p-value are the same either way.

alternative "greater": the second column tends to be larger than the first (positive differences); "less": smaller. Note that this is the opposite reading of STATS_PAIRED and STATS_MANN_WHITNEY, where "greater" means the first column is larger.

Only the asymptotic p is computed, as SPSS NPAR TESTS.

Syntax

=STATS_WILCOXON(columns, colFilter, 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.

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):

By name - exact header text (case-sensitive)
- "age,score,total"
Negative rules - exclude by name (prefix with -)
- "-id,-notes" (include all except id and notes)
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.
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.

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
— two-sided =STATS_WILCOXON(Data!A1:T51, "before,after")
— one-sided: after tends to be larger than before =STATS_WILCOXON(Data!A1:T51, "before,after", "greater")

What the result contains

A report that spills below the formula cell:

the title "Wilcoxon Signed-Rank Test (two-sided) for before,after",
Ranks: Negative Ranks, Positive Ranks, Ties, Total — N, Mean Rank, Sum of Ranks (a group without pairs has Sum 0 and an empty Mean Rank),
Test Statistics: "Z (based on negative ranks)" or "(based on positive ranks)" and Asymp. Sig.

How to read the result

Start with Asymp. Sig.: below 0.05 — the two measurements differ.

The Ranks table shows the direction: more (and larger) positive ranks — the second column tends to be higher.

Use it instead of the paired t test for rating scales, or when the differences are far from normal but roughly symmetric around their centre; its p is a normal approximation, rough with few changed pairs.

In a report: "Scores rose after the course, Z = −2.85, p = .004 (32 rows up, 11 down, 7 unchanged)."

What each number means

Ranks

Group — Group: Read the numbers along the row for that group.
N — N: Shows how many rows went down, went up or stayed the same.
Mean Rank — Mean rank: A higher mean rank means larger changes of that sign. Read it with N and the sum of ranks: more and larger positive ranks — the second measurement tends to be higher.
Sum of Ranks — Sum of ranks: An intermediate number: the test statistic is computed from it.
Negative Ranks (after < before) — Negative ranks: How many rows went down from the first measurement to the second, and the ranks of those changes.
Positive Ranks (after > before) — Positive ranks: How many rows went up from the first measurement to the second, and the ranks of those changes.
Ties (after = before) — Ties: Only the rows that changed count, so many ties make the test weaker.
Total — Total: The rows used — rows where both columns are filled.

Test Statistics

Z (based on positive ranks) — z statistic: A step on the way to p: beyond about ±1.96 a two-sided test is significant at 0.05.
Asymp. Sig. (2-tailed) — Asymptotic significance: This is the p of the test. Below 0.05 — the two measurements differ. In a report: Z = −0.71, p = .480.

How it is computed

The first two selected columns are paired row by row; rows where either is empty are left out.

The differences second − first are taken, as SPSS; zero differences (ties) are left out, the rest are ranked by their absolute size (ties get the average rank) and summed by sign.

Z is the normal approximation of the positive rank sum with the tie correction; the report names the smaller sum, as SPSS. p from the normal distribution, 2- or 1-tailed by alternative (greater: the second column is expected to be larger).

Errors

One cell instead of the report:

one column: "Wilcoxon Signed-Rank Test requires at least 2 groups";
fewer than two complete pairs: "Wilcoxon Signed-Rank Test: need at least 2 observations";
every pair equal: "Wilcoxon Signed-Rank Test: every pair of a and b is equal, the test is not defined";
a text column without value labels: "Wilcoxon Signed-Rank Test needs numeric columns, text columns: t (add value labels or recode to numbers)";
a misspelled alternative, a column name that does not exist — as in the other tests.

About STATS_WILCOXON

STATS_WILCOXON compares two related measurements of the same people, such as scores before and after a course, by ranks. It replaces the paired t test when the differences are not normal or the answers are rating scales. Each row is a pair: the first two selected columns are used, and a row with an empty cell in either column is left out. The report spills below the formula cell and recalculates by itself when new responses arrive from Google Forms.

The difference is the second column minus the first, as SPSS reads a pair, and the Ranks table says it in words, for example "Negative Ranks (after < before)". Pairs with no change go to Ties. More and larger positive ranks mean the second column tends to be higher. Asymp. Sig. is the p-value: below 0.05, the two measurements differ. In a two-sided test Z is never positive, exactly as SPSS prints it; the direction is read from the Ranks table.

Every number of the report is explained on this page in plain words, together with the line to write in your report, for example: Scores rose after the course, Z = −2.85, p = .004 (32 rows up, 11 down, 7 unchanged).