Quick reference
Replace stat with your test statistic (or the cell that holds it) and df with its degrees of freedom.
| You have | Two-tailed p-value | One-tailed (right) p-value |
|---|---|---|
| A z statistic | =2*(1-NORM.S.DIST(ABS(z),TRUE)) | =1-NORM.S.DIST(z,TRUE) |
| A t statistic | =T.DIST.2T(ABS(t),df) | =T.DIST.RT(t,df) |
| A chi-square statistic | Not usually used | =CHISQ.DIST.RT(x,df) |
| An F statistic | Not usually used | =F.DIST.RT(F,df1,df2) |
| Two columns of raw data | =T.TEST(A2:A11,B2:B11,2,3) | =T.TEST(A2:A11,B2:B11,1,3) |
For a left-tailed z test use =NORM.S.DIST(z,TRUE), and for a left-tailed t test =T.DIST(t,df,TRUE).
From a z statistic
NORM.S.DIST(z,TRUE) returns the area to the left of z under the standard normal curve. For a two-tailed test, take the area beyond |z| and double it. With z = −2.17:
| A | B | |
|---|---|---|
| 1 | z | -2.17 |
| 2 | p (two-tailed) | 0.0300 |
From a t statistic
Use T.DIST.2T for a two-tailed test. It needs a positive t, so wrap it in ABS. For t = 2.1 with 24 degrees of freedom:
- =T.DIST.2T(2.1,24) returns 0.0464 (two-tailed)
- =T.DIST.RT(2.1,24) returns 0.0232 (right-tailed, exactly half)
Degrees of freedom depend on the test: n − 1 for one sample or paired data, n₁ + n₂ − 2 for a pooled two-sample test. If you're not sure, the t-test calculator works it out.
Straight from your data with T.TEST
If you have the raw numbers, T.TEST(array1, array2, tails, type) runs the whole test and returns the p-value. tails is 1 or 2; type is 1 (paired), 2 (two-sample, equal variances) or 3 (Welch, unequal variances).
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Group 1 | Group 2 | p-value | |
| 2 | 78 | 72 | 0.0016 | |
| 3 | 85 | 80 | ||
| 4 | 82 | 75 | ||
| 5 | 88 | 78 | ||
| 6 | 75 | 70 | ||
| 7 | 90 | 82 | ||
| 8 | 84 | 76 | ||
| 9 | 79 | 74 | ||
| 10 | 86 | 79 | ||
| 11 | 83 | 73 |
T.TEST returns only the p-value, not t or the degrees of freedom you'd normally report. Run the same data through the t-test calculator to get t = 3.737, df = 17.35, the effect size and every step.
From a chi-square or F statistic
Chi-square and F tests are right-tailed, so you only need the area to the right:
- =CHISQ.DIST.RT(8,4) returns 0.0916 for χ² = 8 with 4 degrees of freedom: not significant at 0.05.
- =F.DIST.RT(3.5,2,27) returns 0.0445 for F = 3.5 with (2, 27) degrees of freedom: significant at 0.05.
- =CHISQ.TEST(observed,expected) takes two ranges of counts and returns the p-value of a goodness-of-fit or independence test directly.
Reading the p-value
Compare the p-value with your significance level α, usually 0.05. If p ≤ α, the result is statistically significant and you reject the null hypothesis. If p > α, you don't have enough evidence to reject it, which isn't the same as proving it true.
Excel shows very small p-values in scientific notation, such as 6.28E-07. That means 0.000000628; report it as p < 0.001. To see the shaded area behind any p-value, paste your statistic into the p-value calculator.
Common mistakes
- Forgetting ABS in two-tailed formulas, which gives an error or a p-value above 0.5.
- Using a one-tailed p-value for a two-tailed hypothesis. It halves p and overstates the evidence.
- Using the old functions (TDIST, CHIDIST, NORMSDIST). They still work but have different argument orders; stick to the newer names above.
- Treating p as the probability that H0 is true. It's the probability of data this extreme if H0 were true.