Guide to Using Excel 2007 For Basic Statistical
Guide to Using Excel 2007 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 7 th Ed. Chapter 10: Hypothesis Testing and Estimation for Two Population Parameters By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2008
Chapter 10 Excel Examples n n Hypothesis Tests – Two Means – SUV Vehicle Mileage Hypothesis Testing – Two Proportions Pomona Fabrications
Hypothesis Test – Two Means SUV Vehicle Mileage Issue: A national car rental agency wishes to test whether SUV vehicles have different mean city mileage versus highway mileage. The premise is that mean highway mileage will exceed mean city mileage Objective: Use Excel 2007 to help test the hypothesis for the difference in population means. Data file is Mileage. xls
Hypothesis Test – SUV Vehicle Mileage Open file Mileage. xls The null and hypothesis to be tested are:
Hypothesis Test – SUV Vehicle Mileage • Select the Data tab • Select Data Analysis • Select Descriptive Statistics • OK
Hypothesis Test – SUV Vehicle Mileage • Input Range = A 1: B 26 Note- This range covers BOTH columns • Grouped by Columns • Labels in First Row • New Worksheet Ply – Descriptive • Summary Statistics • OK
Hypothesis Test – SUV Vehicle Mileage Sample Means, Standard Deviations and Variances are shown for each set of data
Hypothesis Test – SUV Vehicle Mileage Using PHStat’s Box and Whisker tool • Set the Cell Range to A 1: B 26 • Select First cell contains label • Select Multiple Groups – Unstacked • OK
Hypothesis Test – SUV Vehicle Mileage Producing this Box and Whisker Plot Skip No Need thistoslide try this
Hypothesis Test – SUV Vehicle Mileage Using the Data Analysis tool, Select t -test Two-Sample Assuming Equal Variances
Hypothesis Test – SUV Vehicle Mileage • Variable 1 Range: A 1: A 26 • Variable 2 Range: B 1: B 26 • Hypothesized Mean Difference = 0 • Alpha = 0. 05 • New Worksheet Ply = test
Hypothesis Test – SUV Vehicle Mileage Resulting calculations: p value for one tailed test is. 007 which is less than. 05 so we reject the null hypothesis.
Hypothesis Test – Two Proportions Skip rest of slides Pomona Fabrications Issue: The research and development department need to determine whether the new, redesigned motor-heater is more reliable than the current unit. Objective: Use Excel (PHStat) 2007 to help construct a confidence interval estimate for the difference in population proportions. No data is required for this test.
Hypothesis Test – Pomona Fabrications Click on PHStat then Two. Sample Tests then Z Test for Difference in Two Proportions.
Hypothesis Test – Pomona Fabrications • • • Set Hypothesized Difference = 0 Population 1 Level of Significance = 0. 05 Number of Successes = 55 Sample Size = 250 Population 2 Level of Significance = 0. 05 Number of Successes = 75 Sample Size = 250 Lower Tail Test OK
Hypothesis Test – Pomona Fabrications The Lower-Tail test shows that the Null Hypothesis should be rejected
- Slides: 16