Statistics Laboratory #2 Frequency Distributions and Graphs Introduction In order to communicate the significance of dat

[answerhappygod]

Statistics Laboratory 2 Frequency Distributions And Graphs Introduction In Order To Communicate The Significance Of Dat 1 (70.31 KiB) Viewed 86 times

Statistics Laboratory #2 Frequency Distributions and Graphs Introduction In order to communicate the significance of data, it is highly desirable that we display it in some summary form, preferably graphically. One approach that is often used is to construct a frequency distribution that groups data into classes and then determine the number of data that occur in cach class (the frequency). Frequency distributions can be represented graphically in various ways. We will focus on histograms and frequency polygons of quantitative data. Purpose 1. Provide an introduction to Excel functions and procedures that can be used to form and display frequency distributions. 2. Provide an introduction to Excel's graphics capabilities. 3. Identify displays that are most likely to provide insight in given situations. Procedure 1. A sample of the heights of commonly grown herbs is shown on the attached Excel printout. Use this printout to guide you through the remainder of the Procedure. Enter the title Herb Height in cell Al and the data in cells A3 to A21. Create a frequency distribution. a The first step is to enter the class upper limits of [11, 16, 21, 26, 31, 36). Excel refers to the classes as "Bins". Excel has no gaps between classes; a number is in a specific class if it is larger than the upper limit of the previous class but less than or equal to the upper limit of that specific class. b. The second step is to use Excel's Data Analysis package to create the frequency distribution. If you don't see the Data Analysis icon on the Data Tab menu, see "Installing the Data Analysis Package" at the end of this Description i. Click on Data Analysis and select "Histogram" (Excel's name for a frequency distribution) ii. Click on Input Range and then select the range of the sample by clicking on the first cell and dragging to the last. iii. Click on Bin Range and select the range of class upper limits you previously entered (again by click and drag). iv. Select the Output Range button, click on Output Range, and then click on the cell in the spreadsheet where you want the frequency distribution table to begin. v. Finally, click on OK and the frequency distribution will appear. Create a table showing the following columns class name (c.g. 7-11, 12-16, 17-21, etc.) class upper limits (those used in creating the frequency distribution) class midpoints (compute one; successive midpoints differ by class width) frequencies (from the frequency distribution) relative frequencies (computed) 1 Lab 2
The format is up to you but should be pleasing to the eye. 2. Copy Sheet I to Sheet 2. Display the formula page on sheet 2. 3. Copy Sheet I to Sheet 3. Using the information from the table, create a histogram and a relative frequency polygon on Sheet 3: Create a histogram. a. From the Insert Tab, in the Charts group, click on Column and select the top left icon from the menu. A chart frame will appear. b. Right click inside the frame and select "Select Data". The "Select Data Source window will appear. For the "chart data range", select the range of cells in your table containing the frequencies. For the "Horizontal axis labels", select the range of cells in your table containing the Class names. Click on OK. A nearly complete bar chart will appear displaying the frequencies of each class named on the horizontal axis. c. Click on the + button beside the chart and add a chart title and vertical and horizontal axis titles. Experiment with the chart tools Excel provides to fill the bars and add a border to each. Unless you're using a color printer, stick with shades of gray and black. d. Finally, histograms don't have gaps between classes. To eliminate the gaps, right click on one of the bars, select Format Data Series, and move the slider to No Gap. Create a frequency polygon. a. Following the outline for creating histograms above, click on the Line chart and select the top left icon. b. From the Select Data Source window, select the range of cells in your table containing the relative frequencies. The labels on the x-axis of the relative frequency polygon should be the midpoints. c. On the chart, delete the legend name. d. Enter the chart title, vertical and horizontal axis titles as described above. Save your workbook. 4. Setup your workbook for printing by following the instructions in Lab #1. You are going to print all 3 sheets but be sure to do a "Print Preview" for each so that you can see what will print. This allows you to select the page orientation, arrange the content of the page and resize graphs so that each Workbook Sheet will display on a single sheet of paper. When you're close to achieving that, you can choose "1 page" for Width and Height in the "Scale to fit" group of the Page Layout tab so it prints on one page. In Print Preview, compare your Sheet 3 to the attached Excel printout.
5. Answer the following questions: a. In the frequency distribution table on your first page, what does Excel's Histogram function consider as the upper limit of the class labeled "More"? What does it consider as the lower limit of the first class? b. If you wanted to be sure that Histogram function did not include data that were less than the lower limit of your first class, i.e. you did not want the first class to be open ended, what change could you make in the limits you enter for the class limits? c. Assume you are asked to make a presentation to an important client about the effect of your company's SAT tutoring program. You have data from the 100 students from your program and the 50 students from your competitor's program. i. You want to show a chart that makes the point that your competitor's students did not score as well as your firm in the range from 750 - 800? Would you use a histogram, a relative frequency histogram, a frequency polygon, or a relative frequency polygon? Why? ii. If the same number of students from both programs had taken the test, would that have changed your previous answer? Why? Your Report Reminder: be sure your printouts comply with the instructions given in Lab #1. 1. The results page from Procedure step 1. 2. The formula page from Procedure step 2. 3. The results page from Procedure step 3. 4. Answers to the questions specified in #5 above. Additional Instructions for Excel Operations, Installing the Excel Data Analysis Package. . Click on the File tab and select Options. • In the Excel Options window, select Add-Ins. • The selection beside the word Manage should be Excel Add-ins. Click on Go. • Check the box beside Analysis ToolPak and then OK. The Data Analysis Tools package should now be installed and found on the Data tab. Key Excel skills learned. 1. Using the Excel Data Analysis Package. 2. Creating a frequency distribution. 3. Creating an Excel chart and especially histograms and frequency polygons. Prepared by Jim Hannon and Alan tongmuir
G D E G H A B F 1 Frequency distributions, histograms, and frequency polygons. 2 3 Herb height Class midpoint 8.5 13.5 18.5 23.5 28.5 33.5 Frequency Relative frequency 2 0.105 5 0.263 9 0.474 2 0.105 0.000 1 0.053 19 1.000 NONO Herb Height Frequencies 0 12-16 27-31 32-36 5 18 Class Class 6 20 name upper limit 7 18 7-11 11 8 18 12-16 16 9 24 17-21 21 10 10 22-26 26 11 15 27-31 31 12 12 32-36 36 13 20 14 36 15 14 16 20 17 18 10 18 24 8 19 18 6 16 21 16 22 20 23 7 7-11 24 Bins Bin Frequency 25 11 11 2 26 16 16 5 27 21 21 9 28 26 26 2 29 31 31 0 0.500 30 36 36 1 0.400 31 More 0.300 32 0.200 33 0.100 34 35 0.000 8.5 36 37 38 39 Reminder: Answer questions in #5. 40 41 42 17-21 22-26 Height (inches) Herb Height OH Relative frequencies 13.5 28.5 33.5 18.5 23.5 Height (inches)