Welcome! In this tutorial I’m going to be conducting
a one-sample hypothesis z-test in Excel using the built-in Data Analysis command.
I’ll be testing if the population mean differs significantly from 23 based on this sample data.
Since we’re testing if the mean is different from 23,
the alternative hypothesis is µ ≠ 23. And the null hypothesis will be µ= 23.
We will be conducting a z-test because the
population standard deviation is assumed to be 5.
Next I’m going to launch Excel to generate the statistics for the test.
Here in Excel, I’ll be using the Built in Data Analysis command to generate the statistics
for testing the hypothesis.
Since Data Analysis only contains 2 sample z-test,
we’ll create a dummy second variable here. And input at least one 0 in it.
Next, click on the Data tab. And on the right here, click Data Analysis.
Scroll down to the last item here and select z-Test: Two Sample for Means
Click OK.
For Variable 1 range, select the actual data. And for variable 2 range, select the dummy
data.
For the hypothesized Mean Difference, input the parameter 23 (from the hypotheses).
Since the population standard deviation is known to be 5, then the variance for variable 1
will be 5 squared which is 25.
Since we really don’t have a second variable, 0 is the appropriate value for variable 2
variance. But Excel will not allow 0.
So we input a very small value close to 0 here. The value needs to be very small so
its effect on the output is insignificant.
I’m going to check labels because I selected the labels.
For the significance level alpha, I’m going to input .10 here.
And then click OK.
And here’s the output on a different sheet.
I’m going to right click on the Dummy column here and delete because we don’t need it
I’ll change the title to One-sample
And finally remove the word, Difference here.
Here is the cleaned up output from Excel. Let’s set up a critical or rejection region.
The significance level alpha is 0.10. Since this is a 2-tailed test, alpha is divided
by 2 to give 0.05 in each of the 2 tails here. We will use the 2 tailed critical value here.
That is, we will reject the H0 if the z statistic is less than -1.645 or if it is greater than
positive 1.645 For the test statistic, we have z = -1.42
The p-value of the test will be the two-tailed p-value here which rounded gives 0.156.
Next we make a decision on whether or not to reject the null hypothesis.
We can use the rejection region or p-value to make a decision here.
The test statistic -1.42 does not fall into the rejection region.
So we will fail to reject the null hypothesis. Using the p-value, we see that 0.156 here
is greater than our significance level of 0.10.
And that also tells us not to reject the null hypothesis.
We can then conclude as follows: There is not enough evidence to conclude that
the population mean differs significantly from 23.
And that concludes the test. Thanks for watching.