§ Excel and Equations §

Excel- commands used in this topic (Excel in Office 97 and Office 2000)

§ Summary Statistics § Z Test of Hypotheses Spreadsheet §

§ Z Two-Tailed § Z One-Tail Left § Z One-Tail Right §

§ t Hypothesis Test Spreadsheet § t Two-Tailed § t One-Tail Left §

§ t One-Tail Right § Equation Summary §

1. Obtaining "summary statistics" output from Tools-Data Analysis-Descriptive Statistics necessary to do other operations.   If Data Analysis does not show up, then click on Tools-Addins and select the top two options of Analysis Pack and Analysis Pack VBA.

2. Z Test of Hypotheses Spreadsheet- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

Here is a reference spreadsheet showing the location of all important cells used in the hypothesis examples found below:

Data: 240, 250, 230, 245, 235, 241, 239, 242, 238, 243, 237, 244, 236, 246, 234, 248, 232, 249, 231, 250, 230, 230, 250, 210, 270, 211, 269, 200, 280, 199, 281, 195, 285, 190, 290, 240

These sheets utilize the NORMSINV and NORMSDIST dialog boxes:

A. Z Two-Tailed Hypothesis- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

B. Z One-Tail Left Hypothesis Test- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

C. Z One-Tail Right Hypothesis Test- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

3. t Hypothesis Test Spreadsheet- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

See #1 above to find the steps to obtain Summary Statistics.

Here is a reference spreadsheet showing the location of all important cells used in the hypothesis examples found below:

Data: 1.6, 2.6, 0.6, 2.7, 0.5, 2.9, 0.3, 2.9, 0.3, 3.14, 0.06, 3.16, 0.04, 1.6

These sheet utilize the TINV and TDIST dialog boxes:

A. t Two-Tailed Hypothesis- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

B. t One-Tail Left Hypothesis Test- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

C. t One-Tail Right Hypothesis Test- you may use these examples to do hypothesis tests in Excel or you may load the Excel add-in which comes with the Kvanli text book. The add-in makes the process much easier.

Equation Summary

§ Z*and t* § Large-Sample Two-Tailed Tests using Z §

§ Large-Sample One-Tail (left) Tests using Z §

§ Large-Sample One-Tail (right) Tests using Z §

§ Small-Sample Two-Tailed Tests using t §

§ Small Sample One-Tail (left) Tests using t §

§ Small-Sample One-Tail (right) Tests using t §

§ Procedure for Finding the p-value § Finding p-value using t §

§ Using p-value and alpha § no alpha § Other Hypothesis Tests §

1. Z* = [xbar - m_o ] / [s /Ö n]

t* = [xbar - m_o] / [s /Ö n]

2. Large-Sample Two-Tailed Tests using Z on the Population Mean, µ

H_o: m = m_o

H_a: m ¹ m_o

Reject H_o if |Z*| > Z a _/2

FTR (support) H_o if |Z*| £ Z a _/2

3. Large-Sample One-Tail (left) Tests using Z on the Population Mean, µ

H_o: m ³ m_o

H_a: m < m _o

Reject H _o if Z* < - Z a

FTR(Support) H _o if Z* ³ - Z a

4. Large-Sample One-Tail (right) Tests using Z on the Population Mean, µ

H_o: m £ m_o

H_a: m > m _o

Reject H _o if Z* > Za

FTR(Support) H _o if Z* £ Za

5. Small-Sample Two-Tailed Tests using t on the Population Mean, µ

H_o: m = m_o

H_a: m ¹ m_o

Reject H_o if |t*| > t a _{/2, df}

FTR (support) H_o if |t*| £ t a _/2,df

6. Small Sample One-Tail (left) Tests using t on the Population Mean, µ

H_o: m ³ m_o

H_a: m < m _o

Reject H _o if t* < - t a _,df

FTR(Support) H _o if t* ³ - t a _,df

7. Small-Sample One-Tail (right) Tests using t on the Population Mean, µ

H_o: m £ m_o

H_a: m > m _o

Reject H _o if t* > ta _,df

FTR(Support) H _o if t* £ ta _,df

8. Procedure for Finding the p-value:

(b) For H_a: m > m_o, p-value = area to right of Z* or t*

(c) For H_a: m < m_o, p-value = area to left of Z* or t*

9. Finding p-value using t

(a) go to t table

(b) find df row in problem: df = n - 1

(c) locate the value of t* on that row

(d) Note: probabilities are given by the subscript in t a at top of columns

10. Using p-value and alpha

Reject H _o if p-value < a

FTR ( support ) H _o if p-value ³ a

11. When no a:

Reject H_o if p-value < 0.01

FTR ( support ) H_o if p-value > 0.10

Test inconclusive if: 0.01 £ p-value £ 0.10

Other Hypothesis Tests not used at this site:

§ Two-Tailed Tests on the Population Means, m₁ and m₂ using Z §

§ One-Tail (left) Tests on the Population Means, m₁ and m₂ using Z §

§ One-Tail (right) Tests on the Population Means, m₁ and m₂ using Z §

§ Two-Tailed Tests on the Population Proportion, p, using Z §

§ One-Tail (left) Tests on the Population Proportion, p, using Z §

§ One-Tail (right) Tests on the Population Proportion, p, using Z §

§ Two-Tailed Tests on the Population Proportions, p ₁ and p₂, using Z §

§ One-Tail (left) Tests on the Population Proportions, p ₁ and p₂, using Z §

1. Large Independent Samples Hypothesis testing using Z for Two Means

H_o: m ₁ = m₂

H_a: m₁ ¹ m₂

Reject H_o if |Z*| > Z a _/2

FTR (support) H_o if |Z*| £ Z a _/2

Where,

Z* = [(Xbar₁ - Xbar₂) - (m ₁ - m₂ )]/ Ö [(s²₁/n₁ + s ²₂/n₂)]

Which is approximately equal to:

Z* = [(Xbar₁ - Xbar₂) - (m ₁ - m₂ )]/ Ö [(s²₁/n₁ + s ²₂/n₂)]

For n₁ > 30 and n₂ > 30

H_o: m₁ ³ m₂

H_a: m₁ < m₂

Reject H _o if Z* < - Z a

FTR(Support) H _o if Z* ³ - Z a

H_o: m₁ £ m₂

H_a: m₁ > m₂

Reject H _o if Z* > Za

FTR(Support) H _o if Z* £ Za

2. Large Sample Hypothesis testing for a Population Proportion

A. Two-Tailed Tests on the Population Proportion, p, using Z

H_o: p = p₀

H_a: p ¹ p₀

Reject H_o if |Z*| > Z a _/2

FTR (support) H_o if |Z*| £ Z a _/2

Where,

Z* = [(phat) - p ₀]/ Ö {[p ₀(1 - p₀)]/n}

And,

np ₀ > 5 and n(1 - p ₀) > 5

phat = proportion of sample having a specific attribute x

= x/n

H_o: p ³ p ₀

H_a: p < p ₀

Reject H _o if Z* < - Z a

FTR(Support) H _o if Z* ³ - Z a

H_o: p £ p ₀

H_a: p > p ₀

Reject H _o if Z* > Za

FTR(Support) H _o if Z* £ Za

3. Large Sample Hypothesis testing for a Population Proportions, p₁ and p ₂

A. Two-Tailed Tests on the Population Proportions, p ₁ and p_{2, using Z}

H_o: p ₁ = p ₂

H_a: p ₁ ¹ p ₂

Reject H_o if |Z*| > Z a _/2

FTR (support) H_o if |Z*| £ Z a _/2

Where,

Z* = [(p_1hat - p_2hat] /

Ö {[p_1hat (1 - p_1hat)/(n₁ - 1] + [p_2hat (1 - p_2hat)/(n₂ - 1]}

and, p_1hat = x₁/n₁ p_2hat = x₂/n₂

Or a better estimate is given by,

Ö {[pbar (1 - pbar)/(n₁] + [pbar (1 - pbar)/(n₂}

where,

pbar = (x₁ + x₂)/(n₁ + n₂)

p_1hat = x₁/n₁ p_2hat = x₂/n₂

n₁p_1hat > 5 and n₁(1 - p_1hat ) > 5

n₂p_2hat > 5 and n₂(1 - p_2hat ) > 5

B. One-Tail (left) Tests on the Population Proportions, p ₁ and p_{2, using Z}

p₁ and p₂

H_o: p₁ ³ p₂

H_a: p₁ < p₂

Reject H _o if Z* < - Z a

FTR(Support) H _o if Z* ³ - Z a

C. One-Tailed (right) Tests on the Population Proportions,

p₁ and p ₂

H_o: p₁ £ p₂

H_a: p₁> p₂

Reject H _o if Z* > Za

FTR(Support) H _o if Z* £ Za

Go on to Home Work
or
Go back to Hypothesis Testing: Activities and Assignments

Please reference "BA501 (your last name) Assignment name and number" in the subject line of either below.

Back to top