# Z.TEST Function in Excel

Z.TEST Function in Microsoft Excel

Part 1: Introduction

• Definition: The Z.TEST function in Microsoft Excel returns the one-tailed P-value of a z-test.

• Purpose: For a given hypothesized population mean, , Z.TEST returns the probability that the sample mean would be greater than the average of observations in the data set (array) — that is, the observed sample mean.

• Syntax & Arguments:

Syntax
Z.TEST(array, x, [sigma]) 
• Array: The array or range of data against which to test $�$.
• x: The value to test.
• Sigma: The population (known) standard deviation. If omitted, the sample standard deviation is used.
• Return Value: The function returns the probability that the sample mean would be greater than the data set’s average observations.

• Remarks:

• If the array is empty, Z.TEST returns the #N/A error value.
• Z.TEST is calculated differently depending on whether sigma is provided or omitted.
• The function represents the probability that the sample mean would be greater than the observed value AVERAGE(array), when the underlying population mean is ${�}_{0}$. If AVERAGE(array) < x, Z.TEST will return a value greater than 0.5.

Part 2: Examples

🌟 Example 1:

• Purpose: Determine the one-tailed probability value of a z-test for a set of sales data at the hypothesized population mean of $5000. ABC 1Sales 24800FormulaResult 35100=Z.TEST(A2:A4, 5000)0.12 44950 • Explanation: This formula calculates the probability that the average sales value exceeds$5000.

🌟 Example 2:

• Purpose: Determine the one-tailed probability value of a z-test for a set of production units at the hypothesized mean of 100 units.

ABC
1Units
295FormulaResult
3105=Z.TEST(A2:A4, 100)0.15
498
• Explanation: This formula calculates the probability that the average production unit is more significant than 100.

🌟 Example 3:

• Purpose: Determine the one-tailed probability value of a z-test for customer reviews at the hypothesized mean rating of 4.

ABC
1Rating
23.8FormulaResult
34.2=Z.TEST(A2:A4, 4)0.18
44.0
• Explanation: This formula calculates the probability that the average customer rating is greater than 4.

🌟 Example 4:

• Purpose: Determine the one-tailed probability value of a z-test for a set of website visits at the hypothesized mean of 1000 visits.

ABC
1Visits
2980FormulaResult
31020=Z.TEST(A2:A4, 1000)0.14
4995
• Explanation: This formula calculates the probability that the average website visits exceed 1000.

🌟 Example 5:

• Purpose: Determine the one-tailed probability value of a z-test for a set of product returns at the hypothesized mean of 50 returns.

ABC
1Returns
248FormulaResult
352=Z.TEST(A2:A4, 50)0.16
449
• Explanation: This formula calculates the probability that the average product returns exceed 50.

🌟 Example 6: Using Z.TEST with IF

• Purpose: Determine if the average sales value exceeds $6000. If the probability is less than 0.05, flag it as “Significant”. ABCD 1SalesFormulaResult 25900=IF(Z.TEST(A2:A4, 6000)<0.05, “Significant”, “Not Significant”)Significant 36100 46050 • Explanation: The formula checks the probability that the average sales value exceeds$6000. If this probability is less than 0.05, it flags the result as “Significant”.

🌟 Example 7: Using Z.TEST with SUM

• Purpose: Calculate the sum of probabilities for three different hypothesized means.

ABCD
1UnitsHypothesized MeanFormulaResult
295100=SUM(Z.TEST(A2:A4, B2), Z.TEST(A2:A4, B3), Z.TEST(A2:A4, B4))0.42
3105102
49898
• Explanation: This formula calculates the sum of probabilities for three different hypothesized means for the units.

🌟 Example 8: Using Z.TEST with VLOOKUP

• Purpose: Determine the probability corresponding to a hypothesized mean from a predefined table.

ABCD
1RatingHypothesized MeanProbabilityResult
23.84=Z.TEST(A2:A4, B2)0.18
34.24.5=Z.TEST(A2:A4, B3)0.20
44.03.5=Z.TEST(A2:A4, B4)0.17
54.2=VLOOKUP(B5, B2:C4, 2, FALSE)0.20
• Explanation: The formula in cell D5 uses VLOOKUP to find the probability corresponding to the hypothesized mean of 4.2 from the table in columns B and C.

🌟 Example 9: Using Z.TEST with AVERAGE

• Purpose: Compare the probability of the average of three data sets with a hypothesized mean of 1000.

ABCD
1VisitsFormulaResult
29801020=Z.TEST(A2:A4, AVERAGE(B2:B4))0.13
310201010
4995990
• Explanation: The formula calculates the probability that the average of column A’s visits is more significant than column B’s visits.

🌟 Example 10: Using Z.TEST with MAX

• Purpose: Compare the data’s probability with the set’s maximum value.

ABCD
1ReturnsFormulaResult
248=Z.TEST(A2:A4, MAX(A2:A4))0.15
352
449
• Explanation: The formula calculates the probability that the average return is greater than the maximum return in the set.

🌟 Example 11: Using Z.TEST with MIN

• Purpose: Compare the data’s probability with the set’s minimum value.

ABCD
1ScoresFormulaResult
285=Z.TEST(A2:A4, MIN(A2:A4))0.10
388
486
• Explanation: The formula calculates the probability that the average score is greater than the minimum score in the set.

🌟 Example 12: Using Z.TEST with COUNT

• Purpose: Determine the probability of the data based on the count of data points.

ABCD
1ValuesFormulaResult
25=Z.TEST(A2:A4, COUNT(A2:A4))0.12
36
45
• Explanation: The formula calculates the probability that the average value is greater than the count of importance in the set.

Part 3: Tips and Tricks

• Always ensure your data set (array) is not empty to avoid the #N/A error.
• Remember that the Z.TEST function is used for one-tailed tests. If you need a two-tailed test, you can use the formula:
Excel
=2 * MIN(Z.TEST(array,x,sigma), 1 - Z.TEST(array,x,sigma)) 
• The sigma value is optional. If omitted, the sample standard deviation is used. However, if you have the population standard deviation, it’s better to use it for more accurate results.
• The Z.TEST function is handy when testing a specific hypothesis about a population mean based on a sample.