Exercise 4

1. You want to drop the students’ worst test score (not project) and replace it with the final exam, but only if the final exam score is better. Incorporate this policy in your course average computation and redo the course averages. So for some students, if the final exam score is the worst, then no replacement happens.

a) Copy the data about the students i.e. name, the two test scores, two project scores and the final exam points along with the labels and the weights onto a new excel sheet.

b) Create a new column besides the final exam score and label it appropriately say “Best Test Score” In this column we would record the best test score among the two test scores for the student.

c) To decide the best test score we can use the MAX function of excel.

d) In the cell which finds out a student’s best test score write the formula =MAX (Test1_Score, Test2_Score) which would return the maximum among the two test scores.

e) Use auto fill function to calculate the best test score for the rest of the students.

f) Now insert another column besides the column which gives the best test score. In this column we will find compare the students lowest test score with the final exam score and report the higher among the two. We need to get the maximum by comparing the minimum test score and the final exam score.

g) To get a student’s lowest test score we can use the MIN function of excel.

h) Then we can use the result we get from the MIN function and compare it with the final score and replace it with the final score if it is higher and keep the test score if the final score is lower.

i) To accomplish the above step write the formula

=MAX (MIN (Test1_Score, Test2_Score), Finalexam_Score)

To see how this works consider the below scenario

Test 1= 90

Test 2 =85

Final = 92

Putting these values into the formula

MAX (MIN (90, 85), 92)

Evaluating the MIN

MAX (85, 92)

Which would give you 92 as the result i.e. replace the lowest test score of 85

Now consider another scenario

Test 1 = 90

Test 2= 92

Final = 85

MAX (MIN (90, 92), 85)

MAX (90, 85)

Which would keep your lowest test score as it’s greater than the final exam score.

j) Now we have done the necessary operations assign the appropriate weights above the columns having the best test score and the highest among the final exam score and the lowest test score. Use SUMPRODUCT function and the auto fill feature to calculate the grades for all the students.

2. Just before the final, students want to know what score they need on the Final Exam to get a B in the course (or an A, or B+, etc.). Create a copy of the spreadsheet with the final exam scores removed. In its place, put in 2 columns – Score needed to make an A and Score needed to make a B. Find a way to compute these items.

a) Copy the data about the students i.e. name, the two test scores, two project scores along with the labels and the weights onto a new excel sheet.

b) Since we do not have the final exam scores calculate the average without the final exam score using the SUMPRODUCT function.

c) After calculating the averages for all the students insert tow columns after the average scores.

d) Label the new columns appropriately i.e. For A, For B etc

e) The logic we will use to calculate the final score required to get an overall A is

20% of Final Exam = (Min Cutoff for A) - (The calculated average).

So the required final exam score would be

((Min Cutoff for A) - (The calculated average))/0.2

f) Implement the above formula for the first student in the appropriate cell to calculate the final exam score he/she would need to get an A. Do remember to freeze the minimum cutoff score using the $ sign.

g) Using the cutoff score for an overall B calculate the final exam score he/she would need to get a B.