## Excel Z Test Function

Excel Z TESTis 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 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 can try to prove that the null hypothesis is false against the alternative hypothesis.

For example, suppose we have the data(6,7,8,9,5,4,10,4) from A7:A14. Then, to calculate the one-tailed probability value of a Z.TEST for the data with a hypothesized population mean of 4, we will use the following formula:

=Z.TEST(A7:A14,4)

= 0.00052.

The Z.TEST function is one such hypothesis test function. It 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 Z.TEST, we need to have two independent data points that are not related to each other or do not affect each other data points. We should 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 the data.

##### Table of contents

### Syntax

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

**array:**This is the range of cells that contains data points against which we need to test**x**.**x:**From the array, the**x**value is to be tested.**sigma:**This is the overall population’s standard deviation. It is an optional argument. If this is omitted, then Excel uses the sample standard deviationSample Standard DeviationSample standard deviation refers to the statistical metric that is used to measure the extent by which a random variable diverges from the mean of the sample.read more.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

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

#### Example #1 – Using the Z.Test Formula

Look at the below data.

We will use this data to calculate the one-tailed probability value of Z.TEST. For this, assume the 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 are as follows:

**So, open the Z.TEST formula in an Excel cell.****Select the array as scores, A2 to A11.****The next argument is “x.” Since we have already assumed the hypothesized population mean is 6, apply this value to this argument.****The last argument is optional, so close the formula to get the Z.TEST value.****It is a one-tailed Z TEST value to get the two-tailed Z.TEST value to multiply this value by 2.**

#### Example #2 – Z TEST Using Data Analysis Option

We can conduct Z.TEST using the “Data Analysis” option in Excel. To compare two means when the variance is known, we use Z.TEST. We can frame two hypotheses here. One is the “Null Hypothesis.” 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.

We will use two students’ scores in multiple subjects for this example.

**Step 1:**First, we need to calculate the variables for these two values using the VAR.P function.

**Step 2:**Go to the “Data” tab and click “Data Analysis.”

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.”

**Step 3:**For the “Variable 1 Range,” select “Student 1” scores. For the “Variable 2 Range,” select “Student 2” scores.

**Step 4:**For “Variable 1 Variance(known),” select “Student 1” variance score, and for “Variable 2 Variance(known),” select “Student 2” variance score.

**Step 5:**Select the “Output Range” as a cell and press “OK.”

We got the result.

We can reject the null hypothesis if **Z < – Z Critical Two-Tailed** **Z > Z Critical Two Tail**.

So, from the Z.TEST result, below are the results.

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

Since it meets 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 the two students do not differ significantly.

### Things to Remember

- All the arguments should be numerical values. Otherwise, we will get #VALUE! Error.
- The array value should contain numbers. Otherwise, we will get a #N/A error.
- The Z.TEST can be applied to large data sets.

### Recommended Articles

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

- T-TestT-TestA T-test is a method to identify whether the means of two groups differ from one another significantly. It is an inferential statistics approach that facilitates the hypothesis testing.read more
- Z Test vs. T-TestZ Test Vs. T-TestThe Z Test is a statistical hypothesis that is used to determine whether the calculated means of two samples differ when the standard deviation is available and the sample size is large. The T test, on the other hand, is used to determine how the averages of different data sets differ from one another when the standard deviation or variance is unknown.read more
- Chi-Square Test in ExcelChi-Square Test In ExcelIn Excel, the Chi-Square test is the most commonly used non-parametric test for comparing two or more variables for randomly selected data. It is a test that is used to determine the relationship between two or more variables.read more
- T-TEST in ExcelT-TEST In ExcelA T-test in excel helps compare the means (average) of two samples and make inferences for the entire populations.read more