Excel provides you with the opportunity to enter your marks into a spreadsheet that will then calculate the percentages and determine grades. This can be imported into Synergy making report writing quicker and easier.

By far the easiest way to do this is to get a markbook ready-made from a department head. Many departments have already made these and, in order to use them, it is just a matter of saving it somewhere, entering the students’ names and then filling in the grades as you mark.

It is not difficult to create one of these markbooks yourself though. To do so you will need to know how to get Excel to work out percentages, use a table to determine grades and use an equation to make the overall grades. To do so, follow the instructions below:

Percentages

The equation to create a percentage looks like this: =D4/25*100. In order to get there, do this:

• Start with = so it knows it is an equation;
• nominate the cell where you will enter the student’s mark (D4);
• divide (/) by the total number of marks for the assignment (25);
• times (*) 100 to get the percent

Fill down the column by clicking the plus at the bottom right of the active cell then drag this down the column and it will automatically adjust the formula for the other cells.

Grades

In order to get Excel to work out your grades, rename Sheet 2 to ‘VLOOKUP’ and insert a table with the scores and their corresponding grades then connect it to the appropriate cell with this formula: =VLOOKUP(H4,VLOOKUP!$A$1:$B$11,2)

The only thing that you have to change in this formula is to make the cell (in this case H4) lead to the right cell in your spreadsheet (the one that contains the percentage).

Fill down the column by clicking the plus at the bottom right of the active cell then drag this down the column and it will automatically adjust the formula for the other cells.

The Overall Percentage and Grade

To get to the overall percentage you just have to add the percentages together relative to their weighting. For two percentages of equal weighting, the equation looks like this:

=(0.5*E4)+(0.5*H4)

Which means =(fifty percent (0.5) times (*) the mark for whatever (E4)) plus (+)(fifty percent (0.5) times (*) the mark for whatever else (H4)).

Unequal weighting looks like this:

=(0.25*E4)+(0.75*H4) where 0.25 equals 25% and 0.75 equals 75%.

Then you put in the VLOOKUP equation again to get the overall grade.

Good luck!