Z Test in Excel

Excel Z Test Function

Excel Z TEST is a kind of hypothesis testHypothesis TestHypothesis Testing is the statistical tool that helps measure the probability of the correctness of the hypothesis result derived after performing the hypothesis on the sample data. It confirms whether the primary hypothesis results derived were correct.read more that is used to test the alternative hypothesis against the null hypothesis. The null hypothesis is a hypothesis that refers to a common statement in general. By conducting a hypothesis test we try to prove that the null hypothesis is false against the alternative hypothesis.

Z-TEST is one such hypothesis test function. This tests the mean of the two sample data sets when the variance is known, and the sample size is large. The sample size should be >= 30; otherwise, we need to use T-TEST. To ZTEST, we need to have two independent data points that are not related to each other or don’t affect each other data points, and data should be normally distributedNormally DistributedNormal Distribution is a bell-shaped frequency distribution curve which helps describe all the possible values a random variable can take within a given range with most of the distribution area is in the middle and few are in the tails, at the extremes. This distribution has two key parameters: the mean (µ) and the standard deviation (σ) which plays a key role in assets return calculation and in risk management strategy.read more.

Syntax

Z.TEST is the built-in function in excel. Below is the formula of the Z.TEST function in excel.

Z.Test Formula
  • Array: This is the range of cells that contains data points against which we need to test X. This is the value of cells against the hypothesis sample mean is to be tested.
  • X: From the array, the Xth value to be tested.
  • Sigma: This is the overall population’s standard deviation. This is an optional argument. If this is omitted, then excel uses the sample standard deviation.

How to Perform the Z Test in Excel? (with Examples)

You can download this Z Test Excel Template here – Z Test Excel Template

Example #1 – Using the Z Test Formula

For example, look at the below data.

Z.Test Example 1

Using this data, we will calculate the One-tailed probability value of Z TEST. For this, assume hypothesis population meansPopulation MeansThe population mean is the mean or average of all values in the given population and is calculated by the sum of all values in population denoted by the summation of X divided by the number of values in population which is denoted by N.read more 6.

The steps to use the Z Test formula in excel is as follows.

  1. So open the Z TEST formula in an excel cell.

    Z.Test Example 1-1

  2. Select the array as scores, i.e., A2 to A11.

    Z.Test Example 1-2

  3. The next argument is “X.” Since we have already assumed the hypothesized population mean is 6, apply this value to this argument.

    Z.Test Example 1-3

  4. The last argument is optional, so close the formula to get the Z TEST value.

    Z.Test Example 1-4

  5. This is a one-tailed Z TEST value to get the two-tailed Z TEST value to multiply this value by 2.

    Z.Test Example 1-5

Example #2 – Z TEST Using Data Analysis Option

We can conduct Z TEST by using the Data Analysis option in excel. In order to compare two means when the variance is known, we use Z TEST. We can frame two hypotheses here, one is the “Null Hypothesis,” and another one is the “Alternative Hypothesis” below is the equation of both these hypotheses.

H0: μ1 – μ2 = 0 (Null Hypothesis)

H1: μ1 – μ2 ≠ 0 (Alternative Hypothesis)

The alternative hypothesis (H1) states that the two population means are not equal.

For this example, we will use Two students’ scores in multiple subjects.

Z.Test Example 1-6
  • Step 1: The first thing we need to do is to calculate the variables for these two values by using the VAR.P function.
ZTest Example 1-7
  • Step 2: Now go to the Data tab and click on Data Analysis.
Z.Test Example 1-8

Scroll down and select z-TestZ-TestZ-test formula is applied hypothesis testing for data with a large sample size. It denotes the value acquired by dividing the population standard deviation from the difference between the sample mean, and the population mean.read more Two Sample for means and click on Ok.

ZTest Example 2-1
  • Step 3: For Variable 1 Range, select “Student 1” scores, and for Variable 2 Range, select “Student 2” scores.
Ztest Example 2-2
  • Step 4: Variable 1 Variance select Student 1 variance score and Variable 1 Variance select Student 2 variance score.
ZTest Example 2-3
  • Step 5: Select the Output Range as a cell and press, Ok.
ZTest Example 2-4

we got the result.

Z.Test Example 2-5

If Z < – Z Critical Two Tailor Z > Z Critical Two Tail, then we can reject the null hypothesis.

So from the ZTEST result, below are results.

  • Z < – Z Critical Two Tail = -1.080775083 > – 1.959963985
  • Z > Z Critical Two Tail = -1.080775083 < 1.959963985

Since it is meeting our criteria, we cannot reject the null hypothesisNull HypothesisNull hypothesis presumes that the sampled data and the population data have no difference or in simple words, it presumes that the claim made by the person on the data or population is the absolute truth and is always right. So, even if a sample is taken from the population, the result received from the study of the sample will come the same as the assumption.read more. So the means of two students do not differ significantly.

Things to Remember

  • All the arguments should be numerical value other-wise. We will get #VALUE!.
  • Array value should contain numbers; otherwise, we will get #N/A error.
  • ZTEST can be applied to large data sets.

Recommended Articles

This has been a guide to Z Test in Excel. Here we will learn how to perform the Z test in excel using its formula along with examples and with a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>