## What Is Z Test In Excel?

Z Test in Excel helps perform hypothesis testing to assess if a finding is statistically significant. And Excel offers an inbuilt

Statisticalfunction,Z.TEST(), which determines the one-tailed P-value of a Z-test.Users can utilize

Z.TEST()in scenarios such as testing if a stock’s average daily return exceeds a certain percentage.

For example, the below table shows a student’s test scores.

Suppose the hypothesized mean for the above sample is **41**. And we have to determine the one-tailed probability of a Z-test that the hypothesized sample mean exceeds the observed average of the above array. Assume the target cell is D1.

Then, using the **Z.TEST()** in the target cell will fetch us the **P value for Z Test in Excel**.

The **Z.TEST()** accepts the given array of test scores and the hypothesized sample mean of **41** as the input arguments. And it returns the **P value for Z Test in Excel** as **0.733208**.

As the **Z.TEST()** return value of **0.733208** is more than **0.5**, it implies that the hypothesized sample mean exceeds the observed average of the above array of test scores.

## Table of contents

###### Key Takeaways

- We can perform Z Test in Excel using the inbuilt function
**Z.TEST**, which helps determine the one-tailed P-value in a Z-test. And a P-value of more than 0.5 indicates the probability of the hypothesized population mean exceeding the observed average of the supplied array is high. - Users can utilize the
**Z.TEST**function to check if an entity exceeds a certain value or performance level. - The
**Z.TEST**function accepts two mandatory arguments,**array,**and**x**, and one optional argument,**sigma**, as input. - The Z-test is also possible using the
**Data Analysis ToolPak**feature.

### Z Test() Excel Formula

The syntax of the function for Z Test in Excel is:

where,

**array**: The data array against which we must test the hypothesized population mean (**x**).**x**: The hypothesized population mean to test.**sigma**: The known population standard deviation.

While the first and second arguments in the above function are mandatory, the third is optional. And if we ignore it, the **Z.TEST()** uses the sample standard deviation.

Furthermore, when using the **Z.TEST()**, the data points should be independent.

### How To Perform Z Test In Excel?

We can use the function for the Z Test in Excel, **Z.TEST()**, in 2 ways, namely,

**Access from the Excel ribbon.****Enter in the worksheet manually.**

#### Method #1 – Access From The Excel Ribbon

Select a target cell for output → select the **Formulas** tab → click the **More Functions** option drop-down → click the **Statistical** option right arrow → select the **Z.TEST** function, as shown below.

The **Function Arguments** window appears. Enter the arguments in the **Array** and **X** mandatory fields and the **Sigma **field if required → click **OK**.

#### Method #2 – Enter In The Worksheet Manually

- Choose a target cell for the output.
- Type
**=Z.TEST(**in the cell. [Alternatively, type**=Z**and double-click the**Z.TEST**function from the Excel suggestions.] - Enter the arguments as cell values or references and close the brackets.
- Press
**Enter**to execute and get the required P-value.

The first table contains a sample of temperatures in Texas.

Suppose the hypothesized population mean value is **35°F**. And we must determine the probability of the hypothesized population mean value exceeding the average of the array of temperature values. Assume the target cell is D2.

Then, here is how to use the **Z.TEST** function in the target cell to determine the required one-tailed probability.

**Select the target cell D2 and enter the Z.TEST().****Press Enter to obtain the required P-value of the Z-test.**

[Alternatively, select the target cell D2 and click**Formulas**→**More Functions**→**Statistical**→**Z.TEST**to apply the function in the chosen cell.

The**Function Arguments**window will open, where we must update the mandatory argument fields.

Clicking**OK**will close the window. And we will obtain the**Z.TEST()**output as**0.000953**.]

The**Z.TEST()**output is less than**0.5**. It implies that the probability of the hypothesized population mean value of**35°F**exceeding the average of the given array of temperature values is low.

### Examples

#### Example #1

The following example explains **one sample Z Test in Excel**.

The first table shows a stock’s daily return rates.

And the second table contains the normally distributed population’s hypothesized mean and known standard deviation.

Suppose the requirement is to determine the one-tailed probability of the Z-test that the population’s hypothesized mean exceeds the observed stock’s average daily return. Assume the target cell is E4.

Then, performing **one sample Z Test in Excel** cell C4 using the **Z.TEST()** will help achieve the required one-tailed P-value.

**Step 1:**Select the target cell E4, enter the**Z.TEST()**, and press**Enter**.

*=Z.TEST(A2:A11,E2,E3)*

The one-tailed P-value is **0.85408**. It implies that the probability of the hypothesized population mean value exceeding the observed average daily return of the stock is high. And thus, the average daily return of the stock is below **4%**.

#### Example #2

Consider a population of patients with a normally distributed stress level and a mean value of 18.

Suppose we must determine if a therapy affects stress levels. So, we consider 15 patients to record their stress levels after monitoring them over a fixed period.

We can perform a two-tailed Z-test in the second table to evaluate if the therapy has a notable positive or negative effect on the patient’s stress levels. The reason for performing a two-tailed test is that the hypothesis is not whether the therapy reduces stress levels.

So, the null hypothesis is that therapy does not affect the patients’ stress levels.

**Step 1:**Select cell D3, enter the below**Z.TEST()**, and press**Enter**.

*=Z.TEST(A2:A16,D2)*

**Step 2:**Select cell D4, enter the formula to determine the two-tailed P-value of the Z test, and press**Enter**.

*=2*MIN(Z.TEST(A2:A16,D2),1-Z.TEST(A2:A16,D2))*

The two-tailed p-value of **0.034** is less than the significance threshold of **0.05**. So, we can reject the null hypothesis. And thus, the Z-test result interpretation is that the therapy significantly affects the patients’ stress levels.

Further, the one-tailed p-value suggests that the chances of the hypothesized population mean value of **18** being more than the average stress level of the sample are low.

#### Example #3

Let us see an illustration for **two sample Z Test in Excel**.

We can opt for this method when the supplied sample size equals or exceeds 30.

The following table shows the normally distributed data of the per day count of deaths in two cities.

Suppose the requirement is to determine whether the mean of the number of per day deaths for the two cities are different. And for that, we pick a random sample of 20 days of data for each city and record the number of deaths.

It is a scenario of **two sample Z Test in Excel**, and we can perform the test using the **Data Analysis ToolPak** feature in the **Data** tab.

The **Data Analysis ToolPak** feature includes a **Z-test in Excel two sample for means **option to conduct the required test.

But first, check the **Data** tab for the option **Data Analysis**. If we do not find the feature, follow the below steps to install it.

**Step 1:**Click**File**→**Options**.

**Step 2:**Choose**Add-ins**from the left menu.

**Step 3:**Click on the inactive**Analysis ToolPak**add-in and click**Go**.

**Step 4:**Select the**Analysis ToolPak**box and click**OK**.

We can now view the **Data Analysis** option in the **Data** tab

**Step 5:**Select cell E1, enter the**VAR.P()**to determine the variance of the**City_ABC**dataset, and press**Enter**.

*=VAR.P(A2:A21)*

Next, select cell E2, enter the **VAR.P()** to determine the variance of the** City_XYZ** dataset, and press **Enter**.

*=VAR.P(B2:B21)*

**Step 6:**Click**Data**→**Data Analysis**.

**Step 7:**In the**Data Analysis**window, click the**Z-test in Excel two sample for means**option. And click**OK**.

The above step opens the 2 sample Z-test window.

**Step 8:**In the 2 sample Z-test window, update the fields as depicted below.

The two fields under **Input** are the data arrays of daily deaths in the two cities. And let the hypothesized mean difference be **0**.

Next, we must enter the variances for the two input arrays, calculated in step 5. And then, check the **Labels** box as we require the column headings in the Z-test output, and **Alpha** should be **0.05**, the significance threshold value.

Further, set the above **Output options **to view the two sample Z-test result in the active worksheet.

**Step 9:**Click**OK**to obtain the below output.

The test statistic is **-4.55963, **and the pertaining P-value is **5.12415E-06**. As the P-value is less than **0.05**, the significance threshold, we can reject the null hypothesis.

Also, if the condition **z < -z Critical two-tail **or **z > z Critical two-tail** holds, we can reject the null hypothesis.

In this case, the **z** value of **-4.55963** is less than the** -z Critical two-tail** value, **-1.959963**. So, one of the conditions to reject the null hypothesis holds.

And thus, the conclusion is that the mean value of the count of per day deaths is considerably different between the two cities.

### Important Things To Note

- For a non-numeric
**x**or**sigma**, the function for Z Test in Excel,**Z.TEST()**, returns the**#VALUE!**error. - For a zero
**sigma**, the**Z.TEST**function throws the**#NUM!**error. - Suppose the supplied
**array**is empty. Then, the**Z.TEST**function output will be the**#N/A**error. - Suppose the specified array range contains only one value. Or, you omit
**sigma**, and the supplied array’s standard deviation is zero. Then, the**Z.TEST**function throws the**#DIV/0!**error.

### Frequently Asked Questions (FAQs)

**1. How does the Z.TEST function calculate the one-tailed P-value?**

The **Z.TEST **function calculates the one-tailed P-value in the following ways:

The **Z.TEST() **uses the below formula when we supply the **sigma** value.**Z.TEST(array,x,sigma) = 1 – Norm.S.Dist((Average(array)-x) / (sigma/√n),TRUE)**

And when we do not specify the **sigma** value, the function calculates using the below formula.**Z.TEST(array,x) = 1 – Norm.S.Dist((Average(array)-x) / (STDEV(array)/ √n),TRUE)**

The term

**n**in the above expressions is the array size (

**COUNT(array)**).

**2. When to use Z Test in Excel?**

We can use Z Test in Excel when the sample size equals or exceed 30, and the sample data is one or two.

**3. How to apply the Z.TEST function in Excel VBA?**

We can apply the **Z.TEST** function in Excel VBA using the below method:**Application.WorksheetFunction.Z_test(Arg1,Arg2,Arg3)**

The arguments in the above method have the same definition as the **Z.TEST()** arguments explained in the **Z Test() Excel Formula** section of the above article.

Here is an example that shows how to use the above method.

The first table contains a data set.

Suppose the hypothesized population mean for the above normally distributed data is **34**. Then, here is how to obtain the one-tailed probability of a Z-test in cell D2 using the Excel VBA method for **Z.TEST()**.**• Step 1: **Keep the active worksheet containing the above data open and access the VBA Editor using **Alt **+** F11**.**• Step 2:** Choose the required **VBAProject** and click on **Module** under the **Insert** tab.

A new **Module** window opens.**• Step 3: **Enter the VBA code to apply the **Z.TEST()** in the target cell.**Sub Z_test()**

Dim ws As Worksheet

Set ws = Worksheets(“Z-test_FAQ”)

ws.Range(“D2”) = Application.WorksheetFunction.Z_test(ws.Range(“A2:A11”), ws.Range(“D1”))**End Sub****• Step 4:** Click the **Run Sub/UserForm **icon to execute the VBA code.

We can now open the active sheet and view the required one-tailed P-value of the Z-test in cell D2.

The P-value of **0.622988** is more than **0.5**. And thus, the probability of the hypothesized population mean value exceeding the observed average of the given array is high.

### Download Template

This article must be helpful to understand the **Z Test In Excel**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to Z Test in Excel. Here, we calculate the P-value using Z.Test function formula, along with examples & downloadable excel template. You may learn more from the following articles –

## Leave a Reply