A sheet for the raw data for this assignment is provided to you. You will create some new columns as displayed below and as explained below. You may start with the raw data file and type your name somewhere on top of the worksheet. Rename the “Sheet1” tab as Gradebook. On this sheet, you will create a gradebook as follows:

Last Name	First Name	Std. ID	Quiz 1	Quiz 2	Quiz 3	Total Quiz Score	Lowest Quiz Score	Best Two Quiz Score	Best two Quiz Score Percent	Exm 1	Exm 2	Class Particp.	Total	Total Percent
Thomas	Steven	U11111	80	75	68	223	68	155	77.50%	94	65	85	399.00	79.80%
alexander	Suzette	U22222	73	86	70	229	70	159	79.50%	93	91	97	440.00	88.00%
Collins	Heather	U33333	56	58	60	174	56	118	59.00%	85	70	60	333.00	66.60%
Jones	Jill	U44444	60	65	55	180	55	125	62.50%	68	62	70	325.00	65.00%
Kramer	Pat	U55555	40	25	35	100	25	75	37.50%	59	55	70	259.00	51.80%
Holdings	Green	U66666	88	95	75	258	75	183	91.50%	92	89	95	459.00	91.80%
Richards	Billy	U77777	58	65	85	208	58	150	75.00%	92	83	88	413.00	82.60%
Rasmussen	Betty	U88888	95	85	76	256	76	180	90.00%	95	94	90	459.00	91.80%
Average			68.75	69.25	65.50	203.50	60.38	143.13	71.56%	84.75	76.13	81.88	385.88	77.18%
Highest			95.00	95.00	85.00	258.00	76.00	183.00	91.50%	95.00	94.00	97.00	459.00	91.80%
Lowest			40.00	25.00	35.00	100.00	25.00	75.00	37.50%	59.00	55.00	60.00	259.00	51.80%
Range			55.00	70.00	50.00	158.00	51.00	108.00	54.00%	36.00	39.00	37.00	200.00	40.00%

In the column for “Total Quiz Score”, you will write a formula for the first student and then copy and paste that formula for the remaining students.

In the column for “Lowest Quiz Score”, again, you will write a formula for the first student and then copy and paste that formula for the remaining students. In the column for “Best Two Quiz Score”, again, you will write a formula for the first student and then copy and paste that formula for the remaining students.

In the column for “Best Two Quiz Score Percent”, again, you will write a formula for the first student and then copy and paste that formula for the remaining students. You will format the column for two decimal places with percentage formatting.

In the column for “Total” you will write a formula that sums the best two quiz scores, the two exm scores and class participation scores. The total score is out of 500 points.

In the column for “Total Percent” you will write a formula that shows the total score as a percentage with percent format and two decimals points.

Add two more columns to this Gradebook (They are not shown in the data above). These two columns will display the letter grade received by each student. In the first grade column, the grade will be computed using the VLOOKUP function, while in the second column it will be computed using the IF function. Both the columns will have identical output, using two completely different approaches.

The grade cutoffs that you will use are: A at 90% or above, B at 80% or above and below 90%, C at 70%, D at 60% and F below 60%. So the grade for the first student will be a C and for the second student a B and so on.

You will format the two Grade columns using conditional formatting so that each grade appears in a different color text and background.

The row for Average should have a formula using a statistical function. You should write the formula once (For Quiz-1 column) and then copy and paste that formula for the remaining columns.

Similarly the rows for Highest, Lowest and Range should have formulas which you should write using statistical functions for the first column and then copy and paste for the remaining columns.

The Last four rows should be formatted for two decimal places. The two percent columns should be formatted for percent. Submit your excel file, named appropriately through Canvas.