Breaking row pairs
The same record must remain on the same row in both columns.
Compare changes in the same matched records
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in G2:G8. Each A-column identity binds its before value in B to its after value in C; retain those row pairs. D2 is two-sided alpha. Report pair count, mean after-minus-before change, standard error of those within-record differences, signed t for a zero mean change, two-sided paired p, and lower/upper t interval endpoints for the mean change. Trailing rows without an identity are unused. Do not treat the before/after columns as independent groups.
Edit the sample report
Apply one numeric value to the actual worksheet. Observations and alpha retain their separate meanings. The complete sample design is validated before an edit replaces a valid input.
Applied value:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G2, G3, G4, G5, G6, G7, G8. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Preserve record alignment, summarize after-minus-before differences and reconcile a two-sided paired test with a change interval.
THE QUICK ANSWER01
Preserve record alignment, summarize after-minus-before differences and reconcile a two-sided paired test with a change interval.
G2
=COUNT($B$2:$B$21)G3
=AVERAGE($C$2:$C$21)-AVERAGE($B$2:$B$21)G4
=LET(d,FILTER($C$2:$C$21-$B$2:$B$21,$A$2:$A$21<>""),STDEV.S(d)/SQRT(COUNT(d)))G5
=G3/G4G6
=T.TEST($B$2:$B$21,$C$2:$C$21,2,1)G7
=G3-T.INV.2T($D$2,G2-1)*G4G8
=G3+T.INV.2T($D$2,G2-1)*G4For the example data
G2 6G3 1.6666666666666667G4 0.6146362971528592G5 2.711630722733202G6 0.04219399670547972G7 0.08669376568329223G8 3.2466395676500412
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
The design selects the calculation
Enter separate formulas in G2:G8. Each A-column identity binds its before value in B to its after value in C; retain those row pairs. D2 is two-sided alpha. Report pair count, mean after-minus-before change, standard error of those within-record differences, signed t for a zero mean change, two-sided paired p, and lower/upper t interval endpoints for the mean change. Trailing rows without an identity are unused. Do not treat the before/after columns as independent groups.
Keep the model assumptions visible
The teaching inputs assume independent randomly sampled records and approximately normal outcomes (normal within-record differences for the paired task). The supplied numbers do not establish randomization, independence or distribution shape. A confidence procedure has a long-run coverage interpretation; p is not the probability that its null hypothesis is true. These are model-based sample calculations, not causal or practical-impact conclusions.
Declared numeric scope
Use 3–20 numeric observations per group, magnitudes at most 10000, positive relevant sample variance at least 1e-10, and alpha 0.01–0.20. Paired identities stay fixed and complete. Independent samples are contiguous with trailing blanks. Absolute paired/Welch t must be at most 20. Unsupported types, holes, zero spread and numerically ambiguous p-versus-alpha thresholds withhold grading instead of changing the data. Keep full precision; nonzero p values cannot pass as zero.
Read the full step-by-step walkthrough
- Retain the matched design
Each before value and after value refer to the same identified record. Row order is part of the data. Independently sorting one numeric column changes the pairs and the question.
- Form the within-record differences
Use after minus before consistently. FILTER removes unused rows using record identities before STDEV.S measures the variation of actual differences.
- Standardize the mean change
Divide the signed mean change by its standard error. The sign gives direction; the two-sided p value uses the absolute magnitude.
- Choose paired, two-sided inference
T.TEST with tails=2 and type=1 calculates the paired two-sided p. It does not return the signed statistic. For the interval, the pair count gives n-1 degrees of freedom.
- Read the interval and direction together
The lower and upper endpoints estimate the mean after-minus-before change. Reversing that subtraction reverses direction and interval signs while leaving the two-sided tail unchanged. A mean change is separate from a causal claim.
Things to watch
Using the spread of either source column
The standard error comes from the within-record differences.
Reading p as the probability of no change
The p value describes tail extremeness under the zero-mean-change model; it is not the probability that the null statement is true.
About this example
Original bounded source reports checked with real formulas and independent quadrature/incomplete-beta targets. Native recalculation and edit/save remain separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.