PART A: Grade Analysis (7 Activities, 10 points each)
Learning objectives: Become familiar with aggregate Functions (Sum, average, standard deviation, max, min, Count, CountA, CountIF, Frequency, SUMPRODUCT, AverageIF, etc.) and complex IF statements, Large and vlookup.
You are given grades in an Excel worksheet (Assign3). This worksheet has student ID, scores of three exams (1, 2, & 3), assignment 1, assignment 2, and project (in the same order). The weight for each category to compute weighted and the maximum grade that a student can receive for each category are given below:
- Find the total weighted score for the semester for each student. Calculate this in two ways:
- In column H, use simple multiplication, division, and addition.
In the Answers worksheet à Copy your formula used to calculate the score for the first student in the sheet.
- In column I, use SUMPRODUCT (check the help menu to learn more).
In the Answers worksheet à Copy your formula used to calculate the score for the first student in the sheet.
- In column J, instead of using all 3 exams in the weighted score, pick the best TWO SCORES of the 3 exams. For example, if your scores are 95, 82, and 75 in the exams, we drop the lowest score 75. Now, you take these two scores out of 100 and weight that for 60% of your final score instead of 40%. Please still use 280 as the Total Max Score. Think how you would do this. There are many ways to do this.
In the Answers worksheet à Copy your formula used to calculate the score for the first student in the sheet.
- In column K, Repeat iii) using Large function (check the help menu to learn more)
In the Answers worksheet à Copy your formula used to calculate the score for the first student in the sheet.
- Find the Average (=AVERAGE), Standard deviation (=STDDEV), maximum (=MAX), and minimum (=MIN) for each column (all grades and weighted scores). Also, for each of the exams find the average scores of students who received more than 45 and those who received less than 40 (Check out AverageIF or AverageIFs).
- In column L, based on the total score calculated from activity a.iii (i.e., the weighted score based on the best two scores of the 3 exams), assign a grade for each student using the following lookup table (which is also provided in the Excel spreadsheet in cells from O6 to Q13).
In the Answers worksheet à Copy your formula used to calculate the score for the first student in the sheet.
Grade lookup table:
(d) Find the frequency of students’ final Grades that fall into each of the score ranges. Please use the total score calculated from activity a.iii.
Here, please use two ways to calculate the frequency.
First, please use Excel’s FREQUENCY function (here you will use the scores and not letter grade)
à Explain why you can’t use letter grade for frequency in the answer sheet. Meanwhile, copy your frequency table in a new sheet, named as Activity_d
Second, please use Excel’s Histogram function. You need to go to menu item Data à Data Analysis à Histogram utility. Histogram does the same function as frequency but is much easier to use. Here the Bin is what is provided in the Grade lookup table. (Note – some of you may not have Analysis Toolpack installed. Please look for Histogram under Help and follow the instructions to Add-in this application to your menu).
Save the output in sheet Activity_d.
(e) In column M, determine whether a student has a consistent high score in Exams 1, 2 and 3 (higher than or equal to 43). In column N, determine whether a student has a consistent low score in both 1, 2, and 3 (less than 35).
(f) Find the number of students who have consistently performed well and the number of students who have consistently performed poorly according to the results in activity (e).
In the Answers worksheet à Copy your formula used to calculate the score for the first student in the sheet.
à Please show both the number of students who have consistently performed well and the number of students who have consistently performed poorly, as well as the Excel formula used for this.
(g) Determine GPA for each student. You may use VLOOKUP to do so (see points corresponding to each grade in the column R next to the vlookup table). Find the average GPA for the class.
Now, if you want to give more (or fewer) As in the class so that the class average reaches as high as 3.45. What is the lowest score (in column P) to get an A?
Hint: You may use Goal Seek function (Data -> What-if Analysis -> Goal Seek) to answer this question. If you need to adjust other thresholds , please make a note of it.
In the Answers worksheet à Please suggest the lowest score to get an A so that the class average GPA is 3.45.
Solution
Instant Download on Payment
