Saturday, October 13, 2012

How to Use Excel's ZTEST Function


1. Learn the syntax for the ZTEST function. It is: ZTEST(array,u0,sigma) where array of data to test the given value against, u0, is the given value to test and sigma is the known standard deviation. ZTEST returns the #N/A error value if array is empty.
2. Calculate ZEST when sigma is supplied as follows: ZTEST(array,u0) = 1 - NORMSDIST(x - u0)/(sigma/square root of n)).
3. Substitute the sample standard deviation if sigma is omitted as follows:
ZTEST(array,u0) = 1 - NORMSDIST((x - u0)/(s/square root of n)).
4. Note that x is AVERAGE(array)), s is the known standard deviation and n is the number of observation in COUNT(array). Furthermore, if AVERAGE(array)
5. Determine the two-tailed probability. This is the probability that the sample mean is further from the given population mean than AVERAGE(array) and is expressed as follows:2 * MIN(ZTEST(array,u0,sigma),1 - ZTEST(array,uo,sigma)) where u0 is the given population mean.
6. Use Excel 2003 and later for improved accuracy of NORMDIST. Previous versions of Excel used a single computation for all values of z, providing accuracy to about seven decimal places.

Blogger news