Exercise 3

a) Enter the names of the students, their test scores and project scores with appropriate labels. Also create the cutoff table.

b) Mention the weights of each test score and the project in a cell above the labels.

c) We use the SUMPRODUCT function of excel to calculate the final average.

d) SUMPRODUCT multiplies corresponding members in given arrays, and returns the sum of those products for example,

if cells A9:A11 contain the values 1,2,3 and B9:B11 contain 10,20,30, then
=SUMPRODUCT(A9:A11,B9:B11)
returns 140, or (1*10)+(2*20)+(3*30)=10+40+90=140.

e) So to calculate the average score for a student with the specified weights we need to write the formula

=SUMPRODUCT (array of cells having the weights, array of cells having the test scores)

=SUMPRODUCT ($B2:$F2, B4:F4) (according to the solutions excel provided) calculates the average for the first student. We need to fix the cells B2:F2 using the $ so that each calculation refers to the same cells for the weights.

f) To calculate the average for the rest of the students, use the auto fill feature of MS excel.

g) To Assign the grades to each student automatically from a table first create a lookup table similar to the one you made in exercise 1 but with different data i.e. the grades and the corresponding cutoffs , as shown below.

Cut off
0	F
59.5	D
69.5	C-
72.5	C
76.5	C+
79.5	B-
82.5	B
86.5	B+
89.5	A-
93.5	A
97	A+

h) Use VLOOKUP (Average Score, Lookup table range, 2) in the final grade cell of the first student to assign the final grade. Keep in mind to freeze the range of the lookup table using the $ signs.

i) Use Auto fill feature to fill in the grades for the other students.