LAB 3: Descriptive Statistics Before you begin this lab, make sure that the Analysis ToolPak Add-in feature is loaded on your computer. To do this with your home PCs, click on the Data option to see if Data Analysis is an option at the far right of the screen. If Data Analysis does not appear, click on the Office Button at the far top left of the screen and then click on Excel Options at the bottom of the menu. Then click on Add-ins and Analysis ToolPak. Click on Go and check Analysis ToolPak. Then click OK. Once it loads, click on Data at the top of the main screen and you should see Data Analysis as an option at the far right. In the lab classroom, it is ready for you to use whenever you use Microsoft Excel. Purpose: In this lab, we will use Excel to generate summary statistics of data. Computing Measures of Center and Variation using the Data Analysis Option We will learn how to compute measures of center directly from a data set. We will be using the data set entitled TestGradeData.XLS. 1. Go to Pilot. 2. Download the three files to your flash drive. 3. Click on TestGradeData.xls to open the dataset. In the File Download dialog box click on Save. 4. Open the data set TestGradeData.XLS by double clicking on the TestGradeData.XLS file that you downloaded to your flash drive. 5. Once the file is opened, rename the Sheet 1 \"GradesDataAnalysisMethod\" by clicking on the Sheet 1 tab and typing in text. 6. To access the Data Analysis feature click on Data in the menu bar, and then click on Data Analysis. 7. In the Data Analysis Dialog Box, double click on Descriptive Statistics. 8. Generate the descriptive statistics for the first column in your data set only (test grades) by giving the following information: a) For Input Range, type the cell addresses for the test scores A2:A33. b) Click Output Range and type D1. This will be the upper left corner of the output for the first column's analysis. c) Click on Summary Statistics box. d) Click OK. 9. Once you have your Summary Statistics box, you will need to make some adjustments. a) You will need to resize your columns to see all of the output. This can be done by clicking and dragging between the letters at the top of the columns. b) Change the title. To do this click on D1 and change \"Column 1\" to \"Test Grades\" in the formula bar. 10. Make sure that the Summary Statistics box looks correct and then insert a copy of your Summary Statistics data into the answersheet. 11. Now generate Summary Statistics for Test Grades for men and women separately. a) First, copy the test grade and gender columns to a new sheet (Sheet 2) by highlighting grade and gender columns. Then go to the Home tab on the ribbon and click on copy located on the clipboard section. Then click on the new sheet, and click paste from the clipboard section of Home. Name the sheet \"Grades By Gender\". b) Highlight your two columns, and then choose Data and Sort. In the dialog box, choose Sort By Gender. Click OK. Your test grades should now be sorted by Gender. c) Obtain two Summary Statistics results by using the Data Analysis option twice, once for female (Input A2:A19, output D1) and once for male (Input A20:A33, output G1). d) Resize your columns and rename titles with female and male. 12. Look at the print preview to make sure that the two Summary Statistics box looks correct and then insert copy of your two Summary Statistics into the answer sheet. (Adjust if needed to keep on one page.) Use your Summary Statistics to answer the following questions. 1. Give a brief description of each measure included in your Summary Statistics box. (Ignore standard error, kurtosis and skewness.) 2. Comment on how the class did on the test as a whole. Mention the mean, median, and standard deviation and what they tell you. 3. Compare test grades for men versus women on the test, mentioning the mean, median and standard deviation. Computing Measures of Center and Variation using Formulas The Data Analysis Option is a quick method to find the measures of center and variation. But this method does not involve knowledge of statistics. There are other ways to calculate these values which require an understanding of how the measures of center and variation are found. 1. Copy the test grade and gender columns from the first sheet to a new sheet (Sheet 3) and name the sheet \"Grade Formulas\". 2. The mean is a measure of center which works by summing up all the test grades and dividing by the total number of test grades. To create a formula that will find the mean test grade: a) Click on C1 and type in \"Sum\". Click on C2 and in the formula bar, type in the equal to sign \"=\" followed by the word \"SUM\". Follow this with a beginning parenthesis \"(\". Then type in the cells that you would like to have added together. In this case, A2:A33. Then put a closing parenthesis and click Enter. b) Click on D1 and type in \"Mean\". Click on D2 and type in an \"=\". Then click on the sum cell that you found in part a. In the formula bar, divide this number by 32 and press the Enter key to get the mean test grade. 3. The standard deviation works by comparing each data value to the mean. To create a formula that will find the standard deviation of test grades: a) Click on E1 and type in \"Differences\". b) Click on E2 and in the formula bar, type in a \"=\". Then type in A2. Then type in a \"D$2\STT 160, Lab 3, Page 1 Name Amanda Dillman HANDOUT FOR LAB 3 Computing Measures of Center and Variation using the Data Analysis Option: 1. Give a brief description of each measure included in your Summary Statistics box. (Ignore standard error, kurtosis and skewness. No numbers are needed. Just describe.) Mean - The average of all the numbers in the data set. Median - The middle number in a group of values ordered. Mode - The number which appears most often in the data set. Standard Deviation - The measure of variation in the data. Shows how close the data is from the mean. Allows for comparison between two or more sets of data. Sample Variance - The average of the squared differences of the mean. Measures informally how far of set of random numbers are spread out from their mean. Range - The difference between the lowest and highest values in a data set. Minimum - The smallest value in the data set. Maximum - The largest value in the data set. Sum - The total of all the values from the data set added together. Count - The total number of values in the data set. 2. Comment on how the class did on the test as a whole. Mention the mean, median, and standard deviation and what they tell you. Mean = 62.3125 Median = 65 Standard Deviation = 16.39225 As a whole, the class score average was approximately 62%. There is a standard deviation of approximately 16. The median tells that the middle score in this data set is 65%. 3. Compare test grades for men versus women on the test, mentioning the mean, median and standard deviation. STT 160, Lab 3, Page 2 Men Women Mean 67% 58.67% Median 76% 59% Standard Deviation 21.32 16.39 The men had a higher mean and median than the women. However, the women have a lower standard deviation meaning that their scores were less spread out from the mean. Computing Measures of Center and Variation using Formulas: 1. Show that the mean and standard deviation found by using the formulas is the same as the mean and standard deviation found using the Data Analysis Option. Mean from formula: xx = x / n Standard deviation from formula: s = s2 = 1994/32 = 62.3125% = 355.64 = 18.85845 Mean from Data Analysis Option = 62.3125% Standard deviation from Data Analysis Option = 18.85845 2. Show how you can use the standard deviation calculated above to find the variance. You can calculate the variance using the standard deviation by squaring the standard deviation. s2 = (s)2 = 18.858452 = 355.6411 STT 160, Lab 3, Page 3 Insert the boxes from Excel Test Grades Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count 62.3125 3.333734 65 84 18.85845 355.6411 -1.43284 -0.0676 62 32 94 1994 32 Female Grades Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count Male Grades 58.66667 3.863691 59 36 16.39225 268.7059 -1.29511 0.145353 50 36 86 1056 18 Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count 67 5.698467 76 84 21.32171 454.6154 -1.39648 -0.52373 62 32 94 938 14 Test Grades 56 44 84 80 36 94 74 84 36 42 84 78 76 52 40 76 66 72 76 46 64 36 72 48 44 46 72 62 32 86 90 46 Gender F M M M F M F M M F F M M F F F F F M F F F F F F M M F M F M M Test Grades Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count 62.3125 3.333734 65 84 18.85845 355.6411 -1.432842 -0.067602 62 32 94 1994 32 Test Grades 56 36 74 42 84 52 40 76 66 72 46 64 36 72 48 44 62 86 44 84 80 94 84 36 78 76 76 46 72 32 90 46 Gender F F F F F F F F F F F F F F F F F F M M M M M M M M M M M M M M Female Grades Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count 58.66667 3.863691 59 36 16.39225 268.7059 -1.295105 0.145353 50 36 86 1056 18 Male Grades Mean Standard Error Median Mode Standard Deviation Sample Variance Kurtosis Skewness Range Minimum Maximum Sum Count Male Grades 67 5.698467 76 84 21.32171 454.6154 -1.396484 -0.523731 62 32 94 938 14