Guide to Using Excel For Basic Statistical Applications
Guide to Using Excel For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 6 th Ed. Chapter 12: Goodness of Fit Tests and Contingency Analysis By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2005
Chapter 12 Excel Examples ® Goodness of Fit Test Woodtrim Products ® Contingency Analysis Benton Industries
Goodness of Fit Woodtrim Products Issue: The company has recently become concerned that the ripsaw may not be cutting to the manufacture’s specifications Objective: Use Excel to determine whether the data set’s mean and standard deviation meet the manufacturer’s specifications. Data file is Woodtrim. xls
Goodness of Fit – Woodtrim Products Open File Woodtrim. xls
Goodness of Fit – Woodtrim Products Label columns and define classes.
Goodness of Fit – Woodtrim Products Use the COUNTIF function to find the number of data values less than -. 02.
Goodness of Fit – Woodtrim Products Again use the COUNTIF only this time subtract the observations in the previous cell to find the number of data values in the current cell.
Goodness of Fit – Woodtrim Products Continue to use COUNTIF while subtracting the observations in the previous cell to find the number of data values in the current cell.
Goodness of Fit – Woodtrim Products Notice change in the COUNTIF function for the final cell. Also, sum the observations.
Goodness of Fit – Woodtrim Products Use the NORMDIST function to find the expected probability of values less than -. 02 given the expected mean and standard deviation.
Goodness of Fit – Woodtrim Products Continue to use the NORMDIST function to generate probabilities, but to get discrete values, subtract out the sum of the preceding cells.
Goodness of Fit – Woodtrim Products To find the Expected Frequencies multiply the Normal Distribution values by 300.
Goodness of Fit – Woodtrim Products Compute the Chi square value in column N using the formula for each combination of cells.
Goodness of Fit – Woodtrim Products Summing the individual values gives the Chi square value.
Goodness of Fit – Woodtrim Products To find the pvalue click on the Function button, then select Statistics and CHITEST.
Goodness of Fit – Woodtrim Products Enter the ranges for the Actual and Expected frequencies.
Goodness of Fit – Woodtrim Products The p-value is determined.
Contingency Analysis Benton Industries Issue: The company was interested in determining the relationship between absenteeism and marital status. Objective: Use Excel to determine whethere is a statistically significant relationship between absenteeism and marital status. Data file is Benton. xls
Contingency Analysis – Benton Industries Open File Benton. xls
Contingency Analysis – Benton Industries Use this Formula to find the expected number of single people with zero absences.
Contingency Analysis – Benton Industries Using similar formulas determine the expected values for all the combinations of absentee rate and marital status Shown in green.
Contingency Analysis – Benton Industries Use this formula to determine the Chi Square value.
Contingency Analysis – Benton Industries To find the Chi Square critical value, click on the function button, then select Statistical and CHIINV.
Contingency Analysis – Benton Industries Enter the appropriate Probability and Degrees of Freedom to find the critical value.
- Slides: 24