Table of Contents
A One Proportion Z-Test in Excel is a statistical test used to compare the proportion of successes in a sample to a known or hypothesized population proportion. It can be performed in Excel by entering the sample data into a two-column table, entering a formula to calculate the test statistic and then using the appropriate Z-table to find the corresponding p-value. The p-value can then be used to determine if the sample proportion is significantly different from the known or hypothesized population proportion.
A one proportion z-test is used to compare an observed proportion to a theoretical one.
For example, suppose a phone company claims that 90% of its customers are satisfied with their service. To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service, to which 85% responded yes.
We can use a one proportion z-test to test whether or not the true percentage of customers who are satisfied with their service is actually 90%.
Steps to Perform a One Sample Z-Test
We can use the following steps to perform the one proportion z-test:
Step 1. State the hypotheses.
The null hypothesis (H0): P = 0.90
The alternative hypothesis: (Ha): P ≠ 0.90
Step 2. Find the test statistic and the corresponding p-value.
Test statistic z = (p-P) / (√P(1-P) / n)
where p is the sample proportion, P is the hypothesized population proportion, and n is the sample size.
z = (.85-.90) / (√.90(1-.90) / 200) = (-.05) / (.0212) = -2.358
Use the with a z score of -2.358 and a two-tailed test to find that the p-value = 0.018.
Step 3. Reject or fail to reject the null hypothesis.
First, we need to choose a significance level to use for the test. Common choices are 0.01, 0.05, and 0.10. For this example, let’s use 0.05. Since the p-value is less than our significance level of .05, we reject the null hypothesis.
Since we rejected the null hypothesis, we have sufficient evidence to say that it’s not true that 90% of customers are satisfied with their service.
How to Perform a One Sample Z-Test in Excel
One Sample Z Test (Two-tailed)
A phone company claims that 90% of its customers are satisfied with their service. To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service, to which 190 responded yes.
Test the null hypothesis that 90% of customers are satisfied with their service against the alternative hypothesis that not 90% of customers are satisfied with their service. Use a 0.05 level of significance.
The following screenshot shows how to perform a two-tailed one sample z test in Excel, along with the formulas used:
You need to fill in the values for cells B1:B3. Then, the values for cells B5:B7 are automatically calculated using the formulas shown in cells C5:C7.
Note that the formulas shown do the following:
- Formula in cell C5: This calculates the sample proportion using the formula Frequency / Sample size
- Formula in cell C6: This calculates the test statistic using the formula (p-P) / (√P(1-P) / n) where p is the sample proportion, P is the hypothesized population proportion, and n is the sample size.
- Formula in cell C6: This calculates the p-value associated with the test statistic calculated in cell B6 using the Excel function NORM.S.DIST, which returns the cumulative probability for the normal distribution with mean = 0 and standard deviation = 1. We multiply this value by two since this is a two-tailed test.
Since the p-value (0.018) is less than our chosen significance level of 0.05, we reject the null hypothesis and conclude that the true percentage of customers who are satisfied with their service is not equal to 90%.
One Sample Z Test (One-tailed)
A phone company claims that at least 90% of its customers are satisfied with their service. To test this claim, an independent researcher gathered a simple random sample of 200 customers and asked them if they are satisfied with their service, to which 176 responded yes.
Test the null hypothesis that at least 90% of customers are satisfied with their service against the alternative hypothesis that less than 90% of customers are satisfied with their service. Use a 0.1 level of significance.
The following screenshot shows how to perform a one-tailed one sample z test in Excel, along with the formulas used:
You need to fill in the values for cells B1:B3. Then, the values for cells B5:B7 are automatically calculated using the formulas shown in cells C5:C7.
Note that the formulas shown do the following:
- Formula in cell C5: This calculates the sample proportion using the formula Frequency / Sample size
- Formula in cell C6: This calculates the test statistic using the formula (p-P) / (√P(1-P) / n) where p is the sample proportion, P is the hypothesized population proportion, and n is the sample size.
- Formula in cell C6: This calculates the p-value associated with the test statistic calculated in cell B6 using the Excel function NORM.S.DIST, which returns the cumulative probability for the normal distribution with mean = 0 and standard deviation = 1.
Since the p-value (0.17) is greater than our chosen significance level of 0.1, we fail to reject the null hypothesis. We do not have sufficient evidence to say that the true percentage of customers who are satisfied with their service is less than 90%.