Z TEST in Google Sheets

What is Z TEST in Google Sheets?

The ZTEST function in Google Sheets is used to find the probability that a sample mean is greater than the original mean. It determines if there is a significant statistical difference between two populations. In other words, the ZTEST is a statistical function that helps you find the probability that a sample mean is similar to the mean of a known population. It is very useful to test an assumption about a dataset in hypothesis testing.

To take a real-life example, you can test if the average sales in one store region significantly vary from that of another. In such cases, ZTEST does those difficult calculations for you in a jiffy. For example, we have a set of sample data in cells A2:A6. The mean of the population is 40, and the standard deviation is 8. Let us test whether the sample comes from a population with a mean of 40. We apply the formula:

=ZTEST(A2:A6, 40, 8).

The result is the one-tailed p-value for the z-test. A small p-value of less than 0.05 means the sample mean differs greatly from the population mea006E.

The ZTEST function is used to determine whether there is a significant difference between the sample mean and the population mean when the population’s standard deviation is known.
The syntax of Z TEST in Google Sheets is as follows:

=ZTEST(data, mean, standard_deviation)

data: The range of sample data

mean: The known population mean

standard_deviation: The known population standard deviation.

The Z.TEST function will return the p-value.
If the p-value is less than 0.05, it means that the difference between your sample mean, and the population mean is statistically significant.

Syntax

Below is the syntax for the ZTEST function in Google Sheets:

=ZTEST(data, value, [standard_deviation])

Here,

data: The range containing the sample data.

value: The population mean to which you’re comparing the sample data.

standard_deviation: (optional) known standard deviation of the population. If omitted, ZTEST will use the sample standard deviation. It is the Z test in Google Sheets formula.

How to Use Perform Z TEST in Google Sheets?

In statistics, the ZTEST function in Google Sheets performs a Z-test on a sample dataset to determine whether its mean is significantly different from the population mean. The function can be entered in two ways.

Entering Z TEST in Google Sheets manually
Through the Google Menu bar

Enter Z TEST in Google Sheets Manually

Now, let us understand the details of using ZTEST in Google Sheets.

Step 1: Enter the sample data into Google Sheets. For example, we have the score of a group of 10 students. We enter them in column A.

Step 2: Calculate the mean of the sample data using the AVERAGE function. Enter the following formula in C2.

=AVERAGE(A1:A10)

Similarly, find the standard deviation using the STDEV function as follows:

=STDEV(A1:A10)

Step 3: Now, use the ZTEST function. Enter the function as follows:

=ZTEST(A1:A10, 82.2, 11.59)

82.2 is the known population mean you’re comparing the sample against. 11.59 is the calculated standard deviation.

Using the Google Menu Bar

We can also enter the function through the Google Menu bar.
Go to the menu bar and click on “Insert” ➝ “Function” ➝ “Statistical” ➝ “ZTEST.”
Enter the argument as a range or direct values. Close the parentheses and press the “Enter” key.

Examples

ZTEST is an important tool for statistical analysis and hypothesis testing. The ZTEST function calculates the z-score. It is the number of standard deviations that the sample mean is away from the population mean. Let us look at some interesting examples below to understand the function better.

Example #1

In this example, let us compare the average score of several students in a class to the class average. The class average score is 70, and the class standard deviation is 15. Let us use the ZTEST to compare their means.

Step 1: Enter the student scores in cells A2:A7. Go to the next step.

Step 2: Let us enter the class average score of 70. The class standard deviation of 15.

Step 3: Let us use the Z.TEST function to compare the sample mean to the population mean.

Type the following formula:

=Z.TEST(A2:A7, 70, 15). Press Enter.

Explanation:

A2:A6: The range containing your sample data (the 5 student scores).
70: The known population mean (class average score).
15: The population standard deviation.

Step 4: The ZTEST function will return a p-value.

0.006141.

If the p-value is less than 0.05, as in this case, we reject the null hypothesis, which means the average score of the sample group is significantly different from the class average.

If the value is greater than 0.05, it means the average score of the sample group is not significantly different from the class average.

Example #2 – Compare New Product Sales to Historical Sales

Let’s look into a practical Z TEST in Google Sheets example, where we use the ZTEST function in Google Sheets in a goods sales scenario. A company sells a new product over six months, and the company’s historical sales data over six months of the previous year are available. Let us calculate its population mean and analyze whether our product’s daily sales over the past six months significantly differ from this historical average.

Step 1: First, record the number of units of the new product sold for the past six months, in addition to entering the historical data.

Step 2: Now, to calculate the population mean, use the AVERAGE function in Google Sheets.

=AVERAGE(E3:E8)

Also, calculate the standard deviation using the STDEV function.

=STDEV(E3:E8)

Step 3: Now, let’s use the ZTEST function to calculate the p-value and determine if the sample mean is significantly different from the population mean.

In cell B13, enter the following formula:

=ZTEST(B2:B7, C10, C11)

The ZTEST function will return a p-value. Here, the p-value is less than 0.05, which means that the average salary of the sample group is not significantly different from the historical data average.

Example #3 – Compare Employee Salaries

In the example, let us compare the average salaries of employees in different departments to determine whether their wages are significantly different. The ZTEST function works.

The average salary of a company’s HR department is $60,000 per year (population mean), while the standard deviation is $4,000. Now, to compare the salaries of the employees in the HR department and in the R&D department, let us use the ZTEST function in Google Sheets to check if there is a significant difference in the salaries of the two departments.

Step 1: Arrange the data in Google Sheets as shown below.

Step 2: We want to test whether the average salary in the HR department is significantly different from the average salary of $60,000 of the R&D department. To compare them, use the following formula in B7:

=Z.TEST(A2:A10, 60000, 4000)

A2:A10: The range of salaries for employees in R and D.
60000: The HR dept average salary.
4000: The population standard deviation.

Step 3: Check the result. If the p-value ≤ 0.05, you can reject the null hypothesis. If the p-value > 0.05, as seen above, no significant difference exists between the department’s average salary and the company-wide average.

Important Things to Note

The Z TEST in Google Sheets assumes the population’s standard deviation is known and works best for large sample sizes.
It returns a p-value that helps decide whether to reject or accept the null hypothesis.
It is commonly used in quality control.

Frequently Asked Questions (FAQs)

What is the difference between the T-TEST and the Z-Test?

The t-tests and z-tests are used in calculations with sample averages, but there are some differences. A t-test requires two sample arrays while the z-test only needs one. The T TEST is used when the sample size is small and the population standard deviation from a small sample is more appropriate. The distribution follows a t-distribution. The Z TEST is used when the sample size is large, and you have enough data to ensure that the population standard deviation is known. The distribution follows a normal distribution.

What are some of the other similar functions to ZTEST in Google Sheets?

Some of the alternatives to the ZTEST include:

1. TTEST: Used for a small sample size, we use the TTEST. This function is similar to ZTEST but used for situations where the assumptions of ZTEST aren’t noticed.
2. CHISQ.TEST: It performs the Chi-square test for independence or goodness of fit. It compares observed data to expected values.
3. Analysis Toolpak Add-on: The Analysis Toolpak add-on provides a suite of statistical tools for more advanced analyses.
4. F.TEST: It performs an F-test to determine if two populations have different variances.

How to interpret the results of the Z TEST in Google Sheets?

The output of the Z TEST function in Google Sheets is a p-value.

1. If the p-value ≤ 0.05, it means that there is a significant difference between the population mean and the statistical mean.
2. If the p-value is greater than 0.05, there is no statistically significant difference between the two means.

Download Template

This article must help understand Z TEST in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Z TEST Google Sheets Download

Z TEST in Google Sheets