Out-of-the-Box Part 2 - One Sample Mean Testing with Z.TEST

Previously we saw the One Sample Mean test implemented using LAMBDA by calculating the test statistics. Excel provides something similar using the Z.TEST function.

The Z.TEST function compares the average of a sample if it is statistically less than or equal to a test value. The formula is stated as:

Z.TEST(array, x, [sigma])
where
array is the range of data to be tested against x
x is the value of test.

sigma is optional. If the population standard deviation is known, put it here. Otherwise leave it empty, and Excel will use the sample standard deviation instead.

In effect you are testing to see if the average of the array is less than or equal to x

`bar(array) < x`

Comparing for Less Than or Equal To

Observe the box plot of 5 sample distributions above. We want to check if the distributions averages are less than or equal to the value of 50. We see samples A and B less than 50, sample C around 50, and samples D and E more than 50.

NOTE: Where possible do box plots of the distributions. The plots provide a visual aid when you run statistical tests.

We specify the parameters in the Z.TEST function as shown in the example below.

  =Z.TEST(A1:A10, 50)

This tests the data in the range A1:A10 if its average is less than or equal to 50. For the above distributions we get the following results:

The calculations confirms our visual observation: that Samples A, B and C have averages that are less than or equal to 50. 

Comparing for More Than or Equal To

But what if you want to test to see if the average is more than the test value? To do this, we can take the complementary probability of the Z.TEST statistics.

  = 1 - Z.TEST(A1:A10, 50)

When we run the calculations again we get the following table:

The calculations confirm our observation: that Samples C, D and E have averages that are greater than or equal to 50.

Comparing for Equal To

The above two approaches are called one-tailed tests. A two-tailed test is a test for statistical equality. We can also use Z.TEST for this. The test statistics formula is written as:

  = 2 * MIN (Z.TEST(A1:A10, 50), 1 - Z.TEST(A1:A10, 50))

Then we recalculate to get the following table:

Yet again the calculation confirms our suspicion: that only Sample C has an average that is statistically equal to the test value.

When the spread of a distribution does not sufficiently cover the test value `x`, the test statistics will return low values. Such is the case for samples A, B, D and E.

Conclusion

So you can use Excel's Z.TEST function to calculate one mean statistical test. This knowledge is particularly useful when you wish to perform the test but you don't have your LAMBDA functions in place.

My next post will be on two mean statistical test. In the meantime, keep learning from DC-DEN!

Postscript: You may have notices I slowed down on my postings. I have been researching numerical methods with Excel LAMBDA, without the use of Excel's Solver and Goal Seek.