O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

5.6. Performing Statistical Tests

Problem

You're engaged in hypothesis testing and you'd like to learn about Excel's support for conducting statistical tests .

Solution

Excel provides support for conducting standard z-tests, t-Tests, and F-tests. Read the following discussion to learn more.

Discussion

The built-in function ZTEST computes the one-tailed z-test using the normal distribution. The syntax for this function is =ZTEST( cell ref , μ, σ) where cell ref is a cell reference containing an input dataset, μ is the value being tested, and σ is the population standard deviation. If σ is not known, it can be omitted, in which case the sample's standard deviation will be computed and used in the z-test calculation.

You can also compute two-tailed z-tests as follows: =2 * MIN(ZTEST( cell ref , μ, σ), 1 - ZTEST( cell ref , μ, σ)).

The function TTEST computes the one- or two-tailed t-test using Student's t-distribution. Further, TTEST allows you the option of performing paired, two sample with equal variance, or two sample with unequal variance tests. The syntax for TTEST is =TTEST( cell ref 1 , cell ref 2 , tails , type ), where cell ref 1 and cell ref 2 are cell references to the two datasets under consideration and tails is the number of tails to consider for the test (1 or 2). type is a value of 1, 2, or 3, indicating the test type: paired, two sample equal variance, or two sample unequal variance, respectively.

FTEST computes the two-tailed F-test for two datasets. The syntax for FTEST ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required