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.
8. PLEASE SHOW FORMULAS FOR EACH ENTRY

Answer 1

The excel sheet will look like as shown below: (answers are highlighted)

Add column labeled percentage next to Test score in column E

Now, add column labeled Letter Grade next to percentage in column F

Total possible score i.e.200, I have written in cell I2

Grades I have written in ascending order (the reason for that is explained later)

For Calculating percentage

Total possible score i.e.200, I have written in cell I2

In cell E3 write formula "D4/$I$2" (Putting dollar before I and 2 will ensure absolute addressing)

Now, just drag this to calculate percentage for all the students.

Select the cells E3:E15, and and click on the percent symbol shown on the tab (snapshot is attached below with percent symbol highlighted) to convert them into percentage.

For calculating Letter Grade

In cell F3, put formula "VLOOKUP(E3,$H$4:$I$8,2,TRUE)" to determine letter grade. Putting letter grades in ascending order helps in vlookup formula application and assigns the correct grade.

Now, drag this cell to find out letter grades of all other students.

For calculating Average percentage

In any cell use formula "AVERAGE(E3:E15)" and label it Average percentage

For calculating Maximum percentage

In any cell use formula "MAX(E3:E15)" and label it maximum percentage