Spreadsheet Structure

The generated .xlsx workbook will contain several spreadsheet pages:

• Results - a summary sheet containing one column for each assignment or exam. Note that marks are a percentage of the overall contribution to the module. i.e. if an assignment is worth 25% of the module and a student attained 50% for that work a value of 12.5% will be shown. For the percentage attained for a specific assignment refer to the Marks page for the asignment. Statistics for each column are also provided.
• For each assignment or examination four sheets will be generated:
  • Grades - contains a table of the grade to mark mapping and grade based feedback used for each task
  • Picking - contains the grade results entered to the MSG. Marking is possible on this sheet using the same grade based mechanism, however there is no mechanism for detailed feedback entry.
  • Marks - the marks calculated from entered grades, as percentage contributions to the assessment. The raw mark column contains the percentage attained for the assignment, and the module contribution column scales this to give the contribution to the module overall.
  • Feedback - the grade (only) based feedback
• Summary - similar to results with the addition that individual question marks are included for exams. Additional columns to enter fail codes and make adjustments for mark scaling etc.

The sheets of interest are usually the Results and Marks sheets. Results to view the module outcomes as a whole, picking to show the marks for each task.

Example results sheet

Below is the results page for the examples in the effective use section. The complete workbook can be downloaded as CS66610_2012-2013.xlsx.

Example marks sheet

Below is the marks page for the examples in the effective use section. The complete workbook can be downloaded as CS66610_2012-2013.xlsx.

Spreadsheet Formulae

Once upon a time the MFA (Marking Scheme Generator as it was then) did not have a GUI at all, and was purely a way of transforming a marking scheme described in simple xml into a functioning spreadsheet. It soon became clear that even producing simple xml descriptions in a text editor was too much effort, and the GUI was added, together with the ability to enter grades, marks, and feedback directly. Using the GUI for marking relegates the spreadsheets to a convienient output format, and therefore most users will not care about how they work. The xml marking scheme description was extended to include students and marks and is now the .mfax format. However for completeness, and because some users use the MFA as a quick way of generating a robust spreadsheet with all of the weighting formulae to enter exam marks onto the workbook details are now described.

The cell formulae propagate the picking sheet grades through several sheets to the summary sheet. All formulae use 'vlookups' on student id to find the correct row. This ensures that any reordering of a specific sheet, or changes in the rows cannot cause contamination of marks between students. It also makes the formulae complicated, and therefore many cells are protected, causing a dialog box advising where a change should be made to maintain the integrity of the sheets. For information, a summary of the dependencies are shown below:

The following notes explain the consequences of (ignoring any warnings) modifying data at various places in the sheet.

• Overriding the task or question marks on the results sheet will not be propagated to the summary sheet
• Overriding the Raw mark on the results sheet will result in the value being propagated to the Summary sheet. This will clearly also override any task marks.
• Changing marks on the assessments ?_marks sheet will propagate to the results and summary sheet.
• Overriding any entry on the summary sheet will propagate to the results sheet, however the totals will not be calculated from the task entries on this sheet
• Overriding any entry on the summary sheet will not cause the totals to be updated. The Raw mark formula could be changed to do this if necessary.