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:
SyntaxZ.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 . 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.
A B C 1 Sales 2 4800 Formula Result 3 5100 =Z.TEST(A2:A4, 5000) 0.12 4 4950 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.
A B C 1 Units 2 95 Formula Result 3 105 =Z.TEST(A2:A4, 100) 0.15 4 98 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.
A B C 1 Rating 2 3.8 Formula Result 3 4.2 =Z.TEST(A2:A4, 4) 0.18 4 4.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.
A B C 1 Visits 2 980 Formula Result 3 1020 =Z.TEST(A2:A4, 1000) 0.14 4 995 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.
A B C 1 Returns 2 48 Formula Result 3 52 =Z.TEST(A2:A4, 50) 0.16 4 49 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”.
A B C D 1 Sales Formula Result 2 5900 =IF(Z.TEST(A2:A4, 6000)<0.05, “Significant”, “Not Significant”) Significant 3 6100 4 6050 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.
A B C D 1 Units Hypothesized Mean Formula Result 2 95 100 =SUM(Z.TEST(A2:A4, B2), Z.TEST(A2:A4, B3), Z.TEST(A2:A4, B4)) 0.42 3 105 102 4 98 98 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.
A B C D 1 Rating Hypothesized Mean Probability Result 2 3.8 4 =Z.TEST(A2:A4, B2) 0.18 3 4.2 4.5 =Z.TEST(A2:A4, B3) 0.20 4 4.0 3.5 =Z.TEST(A2:A4, B4) 0.17 5 4.2 =VLOOKUP(B5, B2:C4, 2, FALSE) 0.20 Explanation: The formula in cell D5 uses
VLOOKUPto 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.
A B C D 1 Visits Formula Result 2 980 1020 =Z.TEST(A2:A4, AVERAGE(B2:B4)) 0.13 3 1020 1010 4 995 990 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.
A B C D 1 Returns Formula Result 2 48 =Z.TEST(A2:A4, MAX(A2:A4)) 0.15 3 52 4 49 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.
A B C D 1 Scores Formula Result 2 85 =Z.TEST(A2:A4, MIN(A2:A4)) 0.10 3 88 4 86 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.
A B C D 1 Values Formula Result 2 5 =Z.TEST(A2:A4, COUNT(A2:A4)) 0.12 3 6 4 5 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.