60.1K

Verified Solution

Link Copied!

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 # ofpoints out of 200. Letter grades will be assigned using thestandard 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 thisassignment.

1. Create a worksheet with the columns of student names and scoresas shown above.
2. Add a column to the right of Test Score labeled“Percentage”
3. Add a column to the right of Percentage labeled “Lettergrade”
4. Using absolute addressing, calculate the correspondingpercentage score for each student. You must utilizeabsolute addressing in this formula. (hint – put the totalpossible score in one separate cell someplace in your worksheet anduse it for the first student, then copy and paste).
5. Use the AVERAGE function to calculate the average percentageand display with a label of “Average Percentage”.
6. Use the MAX function to calculate the highest percentage scoreand display with a label of “Maximum Percentage.”
7. Using VLOOKUP, determine and display the letter grade for eachstudent.
8. PLEASE SHOW FORMULAS FOR EACH ENTRY