Scribd
Upload
97 views4 pages

Chap8 Excel

This document discusses how to perform hypothesis tests in Excel using built-in functions. It covers hypothesis tests for a proportion, a mean when the standard deviation is unknown, and a mean when the standard deviation is known. For each case, it describes the notation, how to find critical values using functions like NORM.S.INV and T.INV, and how to find p-values using functions like NORM.S.DIST and T.DIST.

Uploaded by

anon_310221965
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
97 views4 pages

Chap8 Excel

This document discusses how to perform hypothesis tests in Excel using built-in functions. It covers hypothesis tests for a proportion, a mean when the standard deviation is unknown, and a mean when the standard deviation is known. For each case, it describes the notation, how to find critical values using functions like NORM.S.INV and T.INV, and how to find p-values using functions like NORM.S.DIST and T.DIST.

Uploaded by

anon_310221965
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

1

Using Excel, Chapter 8:


Hypothesis Testing - One Sample
Excel alone does not conduct complete hypothesis tests1 . However, once you calculate the test statistic, Excel
can get the critical values and the P -values needed to complete the test. The functions used to get critical
values and P -values are demonstrated here.

Chapter 8.2 - Hypothesis Testing About a Proportion

The functions demonstrated here use the standard normal (z) distribution.

Chapter 8.3 - Hypothesis Tests About a Mean: Not Known (t-test)

The functions demonstrated here use the t-distribution.

Chapter 8.4 - Hypothesis Tests About a Mean: Known

The functions demonstrated here use the standard normal (z) distribution.

Excel does actually have two functions, T.TEST and Z.TEST, that return a P -value for a data set but the alternate hypothesis
is awkward (it only conducts right-tailed tests) and you need the raw data.

Chapter 8.2 - Hypothesis Testing About a Proportion


Notation
p p
Test Statistic = zp = q pq
n

Significance Level = (in decimal form)


Critical Values = z or z/2

Finding Critical Values


Here we use the NORM.S.INV function.
NORM.S.INV stands for the inverse of the standard normal distribution (z-distribution).
Usage:
NORM.S.INV(area to the left of the critical value)
This function returns the critical value from the z-distribution provided you put in the appropriate area.
Left-Tailed Tests:

= NORM.S.INV()

Right-Tailed Tests:

= NORM.S.INV(1 )

Two-Tailed Tests: z/2

= NORM.S.INV(/2)

Finding P -Values
Here we use the NORM.S.DIST function.
NORM.S.DIST stands for the standard normal distribution (z-distribution).
Usage:
NORM.S.DIST(z, Cumulative?)
This function returns the area under the curve to the left of z when Cumulative = TRUE.
Left-Tailed Tests: P -value =
Right-Tailed Tests: P -value =
Two-Tailed Tests: P -value =

NORM.S.DIST(zp, TRUE)

zp should be < 0.

1 - NORM.S.DIST(zp, TRUE)

zp should be > 0.

2 (1 NORM.S.DIST( |zp|, TRUE))

Chapter 8.3 - Hypothesis Tests About a Mean: Not Known (t-test)


Notation
Test Statistic = tx =

x
s
n

Significance Level = (in decimal form)


Critical Values = t or t/2
df = degrees of freedom = n - 1

Finding Critical Values


Here we use the T.INV function.
T.INV stands for the inverse of the t-distribution.
Usage:
T.INV(area left of critical value, degrees of freedom)
This function returns the critical value from the t-distribution provided you put in the appropriate area
and degrees of freedom.
Left-Tailed Tests:

= T.INV(, df)

Right-Tailed Tests:

= T.INV(1 , df )

Two-Tailed Tests: t/2

= T.INV(/2, df)

Finding P -Values
Here we use the T.DIST function.
T.DIST stands for the t-distribution.
Usage:
T.DIST(t, df, Cumulative?)
This function returns the area under the curve to the left of t when Cumulative = TRUE.
Left-Tailed Tests: P -value
Right-Tailed Tests: P -value
Two-Tailed Tests: P -value

= T.DIST(tx , df, TRUE)


= 1 - T.DIST(tx , df, TRUE)
= 2 (1 T.DIST( |tx |, df, TRUE))

New to Excel 2010 and higher

T.DIST.RT(tx , df) yields the right-tailed P-value.


T.DIST.2T(tx , df) yields the two-tailed P-value.

Chapter 8.4 - Hypothesis Tests About a Mean: Known


Notation
Test Statistic = zx =

Significance Level = (in decimal form)


Critical Values = z or z/2

Finding Critical Values


Here we use the NORM.S.INV function.
NORM.S.INV stands for the inverse of the standard normal distribution (z-distribution).
Usage:
NORM.S.INV(area to the left of the critical value)
This function returns the critical value from the z-distribution provided you put in the appropriate area.
Left-Tailed Tests:

= NORM.S.INV()

Right-Tailed Tests:

= NORM.S.INV(1 )

Two-Tailed Tests: z/2

= NORM.S.INV(/2)

Finding P -Values
Here we use the NORM.S.DIST function.
NORM.S.DIST stands for the standard normal distribution (z-distribution).
Usage:
NORM.S.DIST(z, Cumulative?)
This function returns the area under the curve to the left of z when Cumulative = TRUE.
Left-Tailed Tests: P -value =
Right-Tailed Tests: P -value =
Two-Tailed Tests: P -value =

NORM.S.DIST(zx , TRUE)

zx should be < 0.

1 - NORM.S.DIST(zx , TRUE)

zx should be > 0.

2 (1 NORM.S.DIST( |zx |, TRUE))

You might also like