StatPakTwo-sample z-test in Google Sheets
The two-sample z-test compares two means when you know each population's variance in advance, for example from years of quality-control records. If you only have the sample variances, use a t-test instead. Example: breaking strength (kg) of cable from two suppliers. Long-run records put Supplier A's variance at 4 and Supplier B's at 4.5.
| A | B | |
|---|---|---|
| 1 | Supplier A | Supplier B |
| 2 | 41.2 | 37.5 |
| 3 | 38.5 | 39 |
| 4 | 44 | 35.2 |
| 5 | 40.1 | 41.4 |
| 6 | 37.8 | 36.8 |
| 7 | 42.6 | 38.1 |
| 8 | 39.9 | 34.9 |
| 9 | 43.1 | 40.2 |
| 10 | 40.7 | 37.7 |
| 11 | 38.2 | 36.3 |
| 12 | 41.9 | 39.6 |
| 13 | 42.3 | 35.8 |
Run the z-test
- Open Extensions, StatPak, Data Analysis and choose z-Test: Two Sample for Means.
- Variable 1 range:
A1:A13. Variable 2 range:B1:B13. - Hypothesized mean difference: 0.
- Variable 1 variance (known): 4. Variable 2 variance (known): 4.5. Enter variances: if you know a standard deviation of 2, enter 4.
- Tick Labels in first row and click Run.
| z-Test: Two Sample for Means | ||
| Supplier A | Supplier B | |
| Mean | 40.85833 | 37.70833 |
| Known Variance | 4 | 4.5 |
| Observations | 12 | 12 |
| Hypothesized Mean Difference | 0 | |
| z | 3.742758 | |
| P(Z<=z) one-tail | 9.1006E-5 | |
| z Critical one-tail | 1.644854 | |
| P(Z<=z) two-tail | 0.0001820117 | |
| z Critical two-tail | 1.959964 |
How to read the output
- z is the difference in means divided by its standard error: (40.85833 - 37.70833) / SQRT(4/12 + 4.5/12) = 3.742758.
- P(Z<=z) two-tail is 0.0001820117. It is below 0.05, and z is beyond z Critical two-tail (1.959964), so the suppliers' mean strengths differ.
- Use the one-tail values (9.1006E-5 and 1.644854) only if you decided in advance to test one direction, such as "Supplier A is stronger".
With formulas
Sheets' Z.TEST function is a one-sample test, so it does not cover this case. Build it directly:
- z:
=(AVERAGE(A2:A13) - AVERAGE(B2:B13)) / SQRT(4/COUNT(A2:A13) + 4.5/COUNT(B2:B13)) - Two-tail p-value:
=2 * (1 - NORM.S.DIST(ABS(z), TRUE)) - Two-tail critical value:
=NORM.S.INV(1 - 0.05/2)
z-test or t-test?
Known population variances are rare outside quality control and textbook exercises. When the variances are estimated from the same data, the t-test accounts for that extra uncertainty. With large samples (a few hundred per group) the two give almost the same answer.