LAB 2: Summarizing and Graphing Data

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 help us summarize and graph sets of data. We will learn how to create frequency distributions and histograms.

Creating a Frequency Distribution and a Histogram

We will learn how to create a frequency distribution directly from a data set. We will be using the data entitled COTININE_2.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
  6. Click on COTININE_2.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 2010 and Excel 2010.
  9. Open the data set COTININE_2.XLS which was saved on your H drive by going to the File Tab and selecting Open. In the Open dialog box, click on the upside down arrow and choose the H drive. Then double click on the COTININE_2.XLS file.
  10. Select the data you will be working with. There are several columns in this data set, but we will be working with the measured levels of serum cotinine for Smokers. The data we want is summarized in column A. Highlight column “A” in your worksheet, including the word title.
  11. Copy the highlighted data by clicking on Home in the ribbon and clicking the copy option from the clipboard section. At the bottom of the worksheet, click on sheet 2. Then paste the highlighted data into this new sheet by clicking on paste in the clipboard section. Rename the sheet “Smokers”.
  12. In cell B1, type the name “Lower Class Limit” or “LCL”. In cell C1, type in “Upper Class Limit” or “UCL”. Type in 0, 100, 200, 300, 400 in the LCL column and 99, 199, 299, 399, 499 in the UCL column.
  13. Click on the Data option and then select Data Analysis. Double click on Histogram.
  14. In the Histogram Dialog Box,

a)You will need to tell Excel what data you want to sort. This is called the Input Range. You can do this by selecting the cells where the data is located in your worksheet or by typing in the cell range in the Input Range box with a colon separating the first and last cell (A2:A41).

b)Excel refers to the upper class limits as bins. In the Bin Range box, select these cells in your worksheet or type in the range of cells where the upper class limit values are located with a colon in between cell names (C2:C6).

c)You will need to tell Excel where to put your results. To put the results in the current worksheet, click on the circle in front of Output Range, and then click inside the entry box. Then type in E1.

d)Click in front of Chart Output in order to get a histogram as well as the frequency distribution.

e)Click on OK.

  1. Once you have your basic frequency distribution, you can make modifications.

a)Delete the row labeled “More”. If you constructed your class limits properly, this should be 0. To do this, highlight the cells containing More and 0. Then right click and select Delete and shift cells up.

b)Rename the first column of the frequency distribution. To do this, click on the cell containing Bin and type in the formula bar what the data represents which in this case is “Cotinine Levels of Smokers”. Resize the column if necessary.

c)A histogram should show either class boundaries or class marks along the horizontal axis. It is too difficult to show class boundaries using Excel, so class marks will be used. Class marks are centered under each bar.

  1. To find class marks, we will use a formula in Excel to compute them for us. Type the formula “=(B2+C2)/2” in a B8. When you press Enter you should see the first class mark of 49.5 in the cell where you entered your formula.
  2. Move the cursor to the bottom right corner of the box containing the first class mark (the 49.5) until it turns into a + sign. Now hold down the left mouse key and “pull” the handle down so that you have 4 additional boxes “selected”. When you let the mouse up, you should see that your cells have been filled with the updated formulas which create the class marks for each of the classes using successive lower and upper class limits.
  3. Replace the Continine Levels of Smokers in E2-E6 with the numbers you found in B8-B12.
  4. Notice your histogram changes as you change the frequency table.
  1. Once you have your basic histogram, you can make modifications.

a)We will need to remove the gaps between bars of the histogram.

  1. To remove the gaps, right click on one of the histogram bars.
  2. Then click on Format Data Series in the menu that is displayed.
  3. Under Series Options, change the Gap Width value to 0%.
  4. Click Close.

b)By clicking on each one, change the title to Cotinine Levels of Smokers and horizontal label to Class Marks.

c)Delete the Legend Box by clicking on it and pressing delete.

d)Resize the histogram by clicking on the histogram and pulling a corner up to the right or down to the left.

  1. Click on a blank cell of the worksheet so that the entire sheet is active. Then select Page Layout on the ribbon and in the Scale To Fit section, change width and height to 1 page. Click on the File Tab and choose Print. Make sure your histogram and data look correct. Then print out a copy of your histogram and data.

Use the ideas from this lab to answer the following questions.

  1. Based on the histogram of your Cotinine data, does the distribution of Cotinine levels for smokers have an approximate normal distribution? Why or why not?
  2. Using Excel and class marks, construct a frequency distribution for the following test scores: (n=25)

(Type in 41, 51, 61, 71, 81, 91 in the LCL column and 50, 60, 70, 80, 90, 100 in the UCL column.)

65 100 74

91 88 89

85 78 83

42 83 80

85 59 94

87 87

79 69

93 89

82 54

75 70

  1. Using the above data, construct a histogram and make sure to remove the gaps. Label the horizontal axis as ‘Test Scores’.
  2. Construct a relative frequency column in your excel worksheet. What should the sum of relative frequencies be equal to?
  3. Based on the test scores histogram, does the distribution of class marks appear to have an approximate normal distribution? Explain.
  4. Click on a blank cell of the test score worksheet so that the entire sheet is active. Then select Page Layout on the ribbon and in the Scale To Fit section, change width and height to 1 page. Click on the File Tab and choose Print. Make sure your histogram and data look correct. Then print out a copy of your histogram and data.

Be sure to include the following in your lab report:

  1. Cover sheet with a summary of the lab you did. It should be one to two paragraphs.
  2. Answer all the questions stated above on the answer sheets provided to get full credit.
  3. Attach the Cotinine worksheet and TestGrade worksheet you were asked to print.