Question

In the table below, there are test scores from a dozen students. The test was worth 200 points. The scores in the table are the # of points out of 200. Letter grades will be assigned using the standard grade boundaries given below.

Last Name	First Name	Test Score
Henry	David	190
Johnson	Sally	100
Olvera	Samuel	170
Chen	Ken	175
Patel	Andrea	198
Johnson	Terry	150
Smith	John	165
Jones	Jonas	180
Swanson	Summer	178
Anderson	Bryce	175
Fish	Jane	166
Ryan	Kathleen	143
Williams	Pat	133

90%	A
80%	B
70%	C
60%	D
< 60%	F

You will need to create a new Excel file for this assignment.

1. Create a worksheet with the columns of student names and scores as shown above.
2. Add a column to the right of Test Score labeled “Percentage”
3. Add a column to the right of Percentage labeled “Letter grade”
4. Using absolute addressing, calculate the corresponding percentage score for each student. You must utilize absolute addressing in this formula. (hint – put the total possible score in one separate cell someplace in your worksheet and use it for the first student, then copy and paste).
5. Use the AVERAGE function to calculate the average percentage and display with a label of “Average Percentage”.
6. Use the MAX function to calculate the highest percentage score and display with a label of “Maximum Percentage.”
7. Using VLOOKUP, determine and display the letter grade for each student.

Answer 1

Last Name	First Name	Test Score	Percentage	Letter grade
Henry	David	190	95%	A
Johnson	Sally	100	50%	F
Olvera	Samuel	170	85%	B
Chen	Ken	175	88%	B
Patel	Andrea	198	99%	A
Johnson	Terry	150	75%	C
Smith	John	165	83%	B
Jones	Jonas	180	90%	A
Swanson	Summer	178	89%	B
Anderson	Bryce	175	88%	B
Fish	Jane	166	83%	B
Ryan	Kathleen	143	72%	C
Williams	Pat	133	67%	D

Average percentage =	82%
Maximum percentage =	99%

Last name	VLOOKUP for Grade	Grade
Henry	= VLOOKUP(H2,A1:E14,5,FALSE)	A
Johnson	= VLOOKUP(H2,A1:E14,5,FALSE)	F
Olvera	= VLOOKUP(H2,A1:E14,5,FALSE)	B
Chen	= VLOOKUP(H2,A1:E14,5,FALSE)	B
Patel	= VLOOKUP(H2,A1:E14,5,FALSE)	A
Johnson	= VLOOKUP(H2,A1:E14,5,FALSE)	C
Smith	= VLOOKUP(H2,A1:E14,5,FALSE)	B
Jones	= VLOOKUP(H2,A1:E14,5,FALSE)	A
Swanson	= VLOOKUP(H2,A1:E14,5,FALSE)	B
Anderson	= VLOOKUP(H2,A1:E14,5,FALSE)	B
Fish	= VLOOKUP(H2,A1:E14,5,FALSE)	B
Ryan	= VLOOKUP(H2,A1:E14,5,FALSE)	C
Williams	= VLOOKUP(H2,A1:E14,5,FALSE)	D