LAB 3 Descriptive Statistics by tuj10580

VIEWS: 0 PAGES: 4

									                              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.    Double click on the Connect to the Internet icon on the desktop.
        2.    Click on Authenticate for Network Access.
        3.    Fill in your username and password.
        4.    Click Submit and then click on The University Portal, WINGS.
        5.    Click on the website address and type in http://www.math.wright.edu/STT_160.html.
        6.    Click on TestGradeData.xls to open the dataset. In the File Download dialog box click on
              Save.
        7.    When the Save As dialog box opens, click on upside down arrow and choose the H drive
              (your personal WSU drive). Click Save. Close the Download dialog box.
        8.    To open Excel, click on the Start Menu, Productivity Apps, Office 2007 and Excel 2007.
        9.    Open the data set TestGradeData.XLS which was saved on your H drive by going to the
              Office Button and selecting Open. In the Open dialog box, click on the upside down arrow
              and choose the H drive. Then double click on the TestGradeData.XLS file.
        10.   Once the file is opened, rename the Sheet 1 “GradesDataAnalysisMethod” by clicking on the
              Sheet 1 tab and typing in text.
        11.   To access the Data Analysis feature click on Data in the menu bar, and then click on Data
              Analysis.
        12.   In the Data Analysis Dialog Box, double click on Descriptive Statistics.
        13.   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.
       14.   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.
       15.   Click on the Office Button, Print, and Print Preview to make sure that the Summary Statistics
             box looks correct and then print out a copy of your Summary Statistics with the data by
             clicking on the Office Button and then Print. Click OK in the Print dialog box.
       16.   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 the Office Button. 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.
       17.   Look at the print preview to make sure that the two Summary Statistics box looks correct
             and then print out a copy of your two Summary Statistics with the data. (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“, which is the value of the mean found in step 2. Click Enter.
              c) Repeat for each data value by clicking on the cell you just finished and clicking copy,
                  and then highlighting 31 cells below it for the remaining test grades and then right
                  clicking and selecting paste. This will give you a column that shows the difference
                  from the mean for each data value.
              d) The next step involves squaring each of the differences. Click on F1 and type in
                  “Squared Differences”.
              e) Click on F2 and in the formula bar, type in a “=”. Type in E2. Then type in a “^2”.
                  Then press Enter.
              f) Repeat for each data value by clicking on the cell you just finished and clicking copy,
                  and then highlighting 31 cells below it for the remaining test grades and then right
                  clicking and selecting paste. This will give you a column that shows the squared
                  differences.
              g) We then need to add up the squares column as done in step 2a using SUM. To do
                  this, click on G1 and type in “Sum of Squares”. Then click on G2 and type the
                  formula “=SUM(F2:F33)” into the formula bar.
              h) Click on H1 and type in “Sum of Squres/n-1”. Click on H2 and in the formula bar
                  type “=G2/31” to divide the sum of squares by n-1.
              i) Click on I1 and type in “Standard Deviation”. Then click on I2 and type in an
                  “=SQRT(H2)” to get the standard deviation for the test grades.
              j) Print out your worksheet showing your excel work. Make sure the work fits on one
                  page.



Using the results from the formulas, answer the following questions.
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.
2. Show how you can use the standard deviation calculated above to find the variance.



Be sure to include the following in your lab report:
A. Cover sheet with a summary of the lab you did. It should be one to two paragraphs.
B. Answer all the questions stated above on the answer sheets provided to get full credit.
C. Attach the three worksheets that you were asked to print.

								
To top