XLSTAT: One Sample T-test or Z-test in Excel

 

Dataset for Example

The attached data correspond to the mathematic grade average for the first semester of a class of 33 students. To compare the class average (11.121) with the county average (11.5), a t-test is appropriate since the variance is unknown. This test assesses whether the difference is statistically significant at a 5% significance level.

 

Setting Up the T-Test

• Open XLSTAT.

• Click on the Test a hypothesis menu and select the One-sample t-test and z-test feature.

  

In the General tab:

• Select the data in column B (“Math average”) using the Range selector.

• Since the data is in a single column for one sample, choose the default sheet option.

• Enable “Column labels” because the column name is included.

• Finally, select the t-test option, as the population standard deviation is unknown.

In the Options tab:

• Select "Mean 1 < Theoretical mean" for a one-sided test.

• Enter 11.5 as the theoretical mean.

• Keep the default significance level of 5%.

In the Missing Data tab, select any option since there is no missing data.

In Outputs, choose Descriptive statistics.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The t-test results indicate we cannot reject the null hypothesis, suggesting the class average performance is similar to the national average.

To go further we can now use the standard deviation for the math grades for the county. Let’s use 0.55. So the variance is 0.3025.

 

Setting up a Z-Test

In the first tab General, this time instead of the Student’s t-test option choose the option z-test .

In the Options tab, you need to set the variance for the z-test. Use the option User defined and set the value to 0.3025.

Click on the OK button to launch the computations.

 

Interpreting the Results

In the results page you now have that the hypothesis H0 should be rejected and that the students performed significantly worse than the students of the county.