Fill This Form To Receive Instant Help
Homework answers / question archive / The Template contains a blank Frequency Distribution table showing grades in numerical and letter grade bins, as well as the raw score data
The Template contains a blank Frequency Distribution table showing grades in numerical and letter grade bins, as well as the raw score data. 3. [50 points] Use the 40 grades data provided to fill in each item in ambercolored Cells D9 through F14. The raw grade scores are included in Cells B16 through B55. a. In the Frequency Count column (Cells D6 through D10), fill in the count of grades in for each bin category. Use a formula in Cell D11 to count the total number of grades. You may find it helpful to use the Excel Sort function on the Data tab to sort the grades first. b. In the Relative Frequency column, provide appropriate formulas in Dells E6 through E10 to calculate the relative frequencies of grades in each bin. Calculate the total in Cell E11. c. In the Cumulative Frequency column, provide formulas in Cells F6 through F10 to calculate the Cumulative frequencies. d. In Cell F12, enter which letter grade has the highest Relative Frequency 4. [30 points] Using the raw numerical scores, use built-in Excel functions to calculate the 5 descriptive statistics Minimum, Maximum, Average, Median and Mode into Cells F15 through F19. NOTE: All of these built in functions are on the Excel Formulas Tab, under More Functions | Statistical. Even better, when you begin typing the first part of any function, Excel will offer to auto-complete it. For example, if you type “=cou” in a cell you will see matching entries. Hitting will insert the highlighted function, ready for you to give the correct range of cells for it to work on. Type a “)” to finish the function entry. The same thing works for “=ave” or “=med” , etc. 5.[20 points] Create two graphs of your Frequency Count by Letter Grade info. After you create each graph, edit it to give it a clear title, and make sure each column or pie wedge is labeled or included in a data legend. a. Create a Grade Frequency Column Chart showing the count of each grade. First open the Insert tab, then select the Letter Grade and Frequency Count data in cells C6 through D10. Then either choose one of the Recommended Charts, or better, click the tiny arrow at right corner to see the list of All Charts. When you select a graph type, you will see several variations. Do NOT choose any of the Stacked Column variations. b. Create a Pie Chart graph of the same grade data. Use a similar procedure to what you used to create the Column Chart. 6. [BONUS 5 points] Write several sentences of your Reflections on this Activity in the Text Box. These can include things like what you learned, what was difficult, how such analyses might be useful, or other observations you have. 7. Upload your completed Excel file back to the same Activity 3 folder in Canvas The Frequency Distribution block is worth 50 points, the Raw Scores Statistics block is worth 30 points, and each Graph is worth 10 points. Thoughtful Reflections are worth a bonus 5 points.
Letter Grade Frequency Distribution
Score Bin Letter Grade Frequency Count Relative Frequency Cumulative Frequency
90 or higher A
80 to 89 B
70 to 79 C
60 to 69 D
59 or lower F
Totals
Most Frequent Letter Grade:
Statistics from Raw Numerical Scores
Raw Scores Grade Minimum:
79 Grade Maximum:
76 Grade Average:
91 Grade Median:
90 Grade Mode:
51
99
70 Insert Bar Chart Graph of Frequency Counts by Letter Grade below
78
83
78
66
51
90
78
76
59
86
82
78
66
81
81
86
78
64
74 Insert Pie Chart Graph of Relative Frequency Percents by Letter Grade below
81
73
92
81
83
79
85
32
91
84
92
70
90
76
Note - if you accidentally mess up the Raw Score data, a backup copy is located on the Saved Data sheet.