Problem 1-Complete in Excel and show formulas and answers

In this problem, you will make a frequency distribution and present the distribution in graphical forms. In cell A3 enter the word "DATA". In cells A4 to A33 enter the following 30 scores on a 10-point quiz: 6, 8, 6, 10, 4, 8, 1, 3, 8, 6, 5, 1, 9, 8, 3, 6, 7, 6, 8, 5, 3, 6, 3, 8, 5, 3, 2, 9, 3, 6,

a. In cell B3 enter the label "Bin". Then in cells, B4 to B13 enter the possible scores, 1 to 10 respectively. (The "bin" identifies the values into which the data will be sorted.) To sort the data into the bins we use the HISTOGRAM tool. From the Excel, Ribbon select the Data tab, then Data Analysis from the Analysis section, and finally HISTOGRAM from the list that appears. This brings up a Histogram Dialog Box. Enter the data, A4..A33, for the Input Range, B4..B13 for the Bin Range, and D3 for the Output Range. Click OK and the frequency distribution should appear in columns D and E. If Data Analysis is not shown the instructor will illustrate how to add this feature to EXCEL.

b. To represent the distribution as a bar (column) graph highlight the Frequency values, E4..E13, and select a column graph from Charts available on the Insert tab. (See Lab 1.) Move the chart if it covers the frequency distribution. To make the columns "touch" right-click one of the columns and choose Format Data Series from the pop-up menu and change the Gap Width to 0.

In a text box explain, for an arbitrary bin value say x, how to determine from the table the percentage of scores that are at least x.