Many hypothesis tests in statistics result in a z-test statistic. Once we find this z-test statistic, we typically find the p-value associated with it. If this p-value is less than a certain alpha level (e.g. 0.10, 0.05, 0.01), then we reject the null hypothesis of the test and conclude that our findings are significant.
This tutorial illustrates several examples of how to find the p-value from a z-score in Excel using the function NORM.DIST, which takes the following arguments:
NORM.DIST(x, mean, standard_dev, cumulative)
where:
- x is the z-score we’re interested in.
- mean is the mean of the distribution – we’ll use “0” for the standard normal distribution.
- standard_dev is the standard deviation of the distribution – we’ll use “1” for the standard normal distribution.
- cumulative takes a value of “TRUE” (returns the CDF) or “FALSE” (returns the PDF) – we’ll use “TRUE” to get the value of the cumulative distribution function.
Let’s check out a couple examples.
Example 1: Finding a P-value from a Z-score (Two-Tailed Test)
A company wants to know whether or not a new type of battery has a different average life than the current standard battery, which has an average life of 18 hours. In a random sample of 100 of the new batteries, they find that the average life is 19 hours with a standard deviation of 4 hours.
Conduct a two-tailed hypothesis test using an alpha level of .05 to determine if the average life of the new battery is different than the average life of the current standard battery.
Step 1: State the hypotheses.
The null hypothesis (H0): μ = 18
The alternative hypothesis: (Ha): μ ≠ 18
Step 2: Find the z-test statistic.
Test statistic z = (x-μ) / (s/√n) = (19-18) / (4/√100) = 2.5
Step 3: Find the p-value of the z-test statistic using Excel.
To find the p-value for z = 2.5, we will use the following formula in Excel: =1 – NORM.DIST(2.5, 0, 1, TRUE)
This tells us that the one-sided p-value is .00621, but since we’re conducting a two-tailed test we need to multiply this value by 2, so the p-value will be .00612 * 2 = .01224.
Step 4: Reject or fail to reject the null hypothesis.
Since the p-value of .01224 is less than our chosen alpha level of .05, we reject the null hypothesis. We have sufficient evidence to say that the average life of the new battery is significantly different than the average life of the current standard battery.
Example 2: Finding a P-value from a Z-score (One-Tailed Test)
A botanist believes that the mean height of a certain plant is less than 14 inches. She randomly selects 30 plants and measures them. She finds that the mean height is 13.5 inches with a standard deviation of 2 inches.
Conduct a one-tailed hypothesis test using an alpha level of .01 to determine if the mean height of this plant is actually less than 14 inches.
Step 1: State the hypotheses.
The null hypothesis (H0): μ≥ 14
The alternative hypothesis: (Ha): μ
Step 2: Find the z-test statistic.
Test statistic z = (x-μ) / (s/√n) = (13.5-14) / (2/√30) = -1.369
Step 3: Find the p-value of the z-test statistic using Excel.
To find the p-value for z = -1.369, we will use the following formula in Excel: =NORM.DIST(-1.369, 0, 1, TRUE)
This tells us that the one-sided p-value is .08550.
Step 4: Reject or fail to reject the null hypothesis.
Since the p-value of .08550 is greater than our chosen alpha level of .01, we fail to reject the null hypothesis. We do not have sufficient evidence to say that the average height of this certain species of plant is less than 14 inches.
For more statistics tutorials in Excel, be sure to check out our complete list of Excel Guides.