Gnumeric · Statistical analysis · Advanced

Use Gnumeric's One-Sample Wilcoxon Signed Rank Test

Check signed ranks, zero differences and Gnumeric 1.12.62's normal-approximation limits with a twelve-observation Wilcoxon example.

Gnumeric 1.12.62 released source reviewed; independent rank arithmetic checked; graphical analysis-tool execution pending · Editorial standards

Gnumeric’s one-sample Wilcoxon Signed Rank Test compares observations with a specified center by ranking the absolute differences and separating their positive and negative rank sums. Unlike the one-sample Sign Test, it uses more than counts above and below the hypothesized value.

Important limitation: Gnumeric 1.12.62’s tool uses a normal approximation, not an exact small-sample calculation. Its released code suppresses the p-value below 12 nonzero numeric differences. Rank ties and nearly balanced rank sums need additional care, as explained below.

Verification scope: The instructions and output reconstruction are based on the English menu, dialog and calculation source released with Gnumeric 1.12.62. Independent arithmetic checks support the examples. The graphical tool and its generated array formulas were not executed for this article, and no native screenshot or cross-platform behavior is claimed.

Choose the right question

For this exercise, the null is a symmetric population centered at 10, with a two-sided alternative and α = 0.05. Independent observations and a defensible symmetry assumption are needed for the usual signed-rank interpretation as a location/median test. The NIST signed-rank reference explains the symmetry assumption. “Nonparametric” does not mean assumption-free.

Do not confuse this with the paired two-sample Wilcoxon choice or the Wilcoxon–Mann–Whitney test for independent groups. The one-sample tool takes one sample and one hypothesized value. Repeated measurements on the same unit are not independent observations merely because they occupy different rows.

Enter and check twelve observations

Put Reading in A1 and the following values in A2:A13. The differences and ranks are shown for manual checking; the tool’s input is the Reading column.

Reading	Reading − 10	Absolute-difference rank
9	−1	1
8	−2	2
7	−3	3
14	4	4
15	5	5
16	6	6
17	7	7
18	8	8
19	9	9
20	10	10
21	11	11
22	12	12

There are no zero differences and no tied absolute differences. Therefore:

Effective N = 12.
Negative rank sum S− = 1 + 2 + 3 = 6.
Positive rank sum S+ = 4 + 5 + … + 12 = 72.
Total rank sum 78 = 12 × 13 / 2.
Test statistic T = min(S−, S+) = 6.
Sample median 16.5, distinct from the hypothesized value 10.

The sign test would see three below and nine above. Wilcoxon instead uses the rank sums 6 and 72. Those are not interchangeable statistics.

Configure the one-sample dialog

Choose Statistics → One Sample Tests → Claims About a Median → Wilcoxon Signed Rank Test…. The released menu structure locates this under One Sample Tests.
On Input, set Input range to A1:A13, choose Grouped by: Columns, and enable Labels. If selecting A2:A13 instead, leave Labels off.
On Test, confirm Wilcoxon Signed Rank Test, not Sign Test. Set Predicted Median to 10 and Alpha to 0.05. Do not substitute the observed median of 16.5 for the hypothesized value.
On Output, choose New sheet to protect the input. Formulæ retains calculations; Values creates a snapshot. Neither automatically extends the selected input range when you append observations.
Run the tool and compare the reconstructed values below. The released shared median-test dialog also supports Rows and Areas grouping, which produce separate tests rather than one pooled sample.

Understand the approximate probability

The released signed-rank calculation uses expected rank sum N(N+1)/4 and variance N(N+1)(2N+1)/24.

For the untied twelve-observation example, these are 39 and 162.5. It evaluates the lower normal tail at T + 0.5, so the standardized value is:

(6 + 0.5 − 39) / SQRT(162.5) ≈ −2.549510

The reconstructed one-tailed row is approximately 0.005393725, and its doubled two-tailed row is approximately 0.010787449. These are independently calculated source predictions, not observed Gnumeric tool output. They are normal approximations with the source’s continuity adjustment, not exact enumerations of all possible rank assignments.

For this two-sided teaching question, the approximate probability is below 0.05 under the stated model. That is not a practical effect-size conclusion and does not validate the sampling or symmetry assumptions. Do not treat the “one-tailed” row as automatically matching a preselected direction: the tool uses the smaller rank sum and discards which side supplied it.

Zero differences are not the same as tied ranks

If you append 10, its difference from the predicted median is zero. The released code excludes that difference from N and the signed-rank sums. The original twelve nonzero ranks remain unchanged, so N stays 12, although there are now thirteen numeric readings. The full sample median changes to 16.

If you remove the reading 22 from the original example, N becomes 11. In the released implementation the one-tail cell returns #N/A, and the doubled row propagates that unavailable result. This is a deliberate approximation-size gate, not a request to add invented observations or replace the result with zero. A literal reading of zero is not omitted when the predicted median is 10; its difference is −10.

Tied absolute nonzero differences are different. For example, replace the reading 14 with 13. The differences −3 and +3 now occupy average ranks 3.5 and 3.5. The negative rank sum becomes 6.5, positive rank sum 71.5, and the total remains 78. The released implementation uses RANK.AVG for ranks, but its probability variance remains the untied expression above: it does not apply a rank-tie variance adjustment. Do not certify a tied-data p-value merely because the rank sums look correct. This tutorial’s probability example deliberately has no ties.

Watch for a probability greater than one

There is a further source-level edge case. With N = 12 and perfectly balanced rank sums 39 and 39, the code’s lower normal tail at 39 + 0.5 exceeds 0.5. Doubling it produces approximately 1.031287, because the released formula does not cap the result at 1.

A probability greater than 1 is not valid. Treat that as an implementation/approximation warning, not strong evidence or an acceptable final result. Preserve the output and independently verify the test with an appropriate method. Merely formatting the cell as a percentage or rounding it does not repair the calculation. This is a prediction from 1.12.62 source and checked arithmetic, not an observed GUI defect in every version.

Troubleshooting and scope boundaries

Unexpected N: inspect zero differences, text numbers, missing readings and whether Labels removed the first observation. Do not assume a visual number is numeric data.
Different rank sums: rank absolute differences from the specified center, not the original readings. Equal absolute differences share average ranks.
Every difference is zero: this is a degenerate sample for this method. Do not infer that a missing p-value proves the null.
Different results from another program: compare zero handling, tie correction, exact versus approximate methods and continuity adjustment before blaming rounding.
Multiple columns: grouping produces separate tests. Repeating tests introduces a separate multiple-testing question that this tutorial does not resolve.
Blanks, text or formula errors: this guide does not certify every coercion/error pathway in the tool’s generated array formulas. Clean the intended numeric sample and retain an audit copy rather than silently filling gaps with zero.