Class Grades

You are to compute grades for a class. This class has 10 assignments and 3 tests. Each assignment and test may be based on a different number of points identified on the input. Each student's earned points are shown in the input. A blank value for a grade should be interpreted as 0. You are to normalize each of these grades to a basis of 100. You are to round each normalized grade to the nearest integer using the built-in ROUND function.

Each of these elements is also assigned a weight indicating how much that item should be weighted. Do NOT assume that the weights total 100; they define the relative weights. The final weighted grades is also to be rounded to the nearest integer using the built-in ROUND function.

All assignments will be counted. Only the 2 highest tests will be counted. The test may be based on different numbers of points, but they will all be weighted equally. The total relative weight of the tests is the weight of 2 tests.

TIPS:

• Look up the function SUMPRODUCT.
• You may want to compute the different parts separately and then add them.

The input for the grades will be on a worksheet titled assignments. There will always be 20 students, 10 assignments and 3 tests.

All of the following values are to be read from the spreadsheet:

• student names [column A]
• relative weight of each assignment [row 1]
• assignment names [row 2]
• total possible points for each assignment [row 3]
• points earned for each assignment

The result page is to be named final grades and it is to have

• the student names
• the assignments names and weights
• the normalized grades (based on 100 points)
• the final grade

It is not to have any other values.

Any intermediate worksheets should have names other than the default Sheetn and should follow the final grade sheet.

The web page is to be a single page formatted as follows:

• the main content of the table is to be in a fixed width div
• the page itself is not to be fixed width and should have a contrasting background, either solid or a picture
• the tab should have an appropriate title
• the page should indicate the teacher, class, and when the class was taught; use your imagination
• the table is to have the same content as the result page
• assignment names and weights should be distinguished (e.g., bold or a different color)
• student names and final grades are also to be distinguished but DIFFERENTLY than the assignment names and weights

TIPS:

• Take advantage of the fact that Mr Data Converter puts classes everywhere.
• Look up the pseudo-element :first-child to help you with your css.

You will be given two sets of sample data and results to work with and a third set of data without results that you are to use in your web page. You are to post your webpage on the UNC webspace in a folder called Assign6 and the page is to be named index.html. This way, the page will be accessed as www.unc.edu/~onyen/Assign6. I will test your spreadsheet with an additional set of data. It is therefore critical that you NOT do any work on the input sheet and that you NOT assume any of the data will stay the same. What WILL remain the same is the number of data entries and the location of all information.

The first set of sample input can be downloaded from Hogwarts data and the expected results found at Hogwarts results. The second set of sample input can be downloaded from Middle Earth data and the expected results found at Middle Earth results. The web data (purposely non-descript) can be doenloaded from web data.

Please be sure to save the spreadsheet to your own machine before beginning work on it. When you want to use different data, copy and paste the data into your spreadsheet.

Grading Criteria: Grading for this assignment is based primarily on the correctness of the results and the usual requirements for the web page. Readability of the results will also be considered.