Marking Scheme and Feedback Assistant - Help - Introduction

Spreadsheet tab

An Excel marking spreadsheet can be generated once a cohort has been loaded (see the Module tab). It contains all the formulas and lookup tables needed to produce the module results as grades are entered, and looks up and transfers information between sheets using the student ID, so it is not affected by reordering the student list afterwards.

Layout parameters

These control the sizing of cells that can't be derived automatically from their contents (dimensions are in characters unless stated otherwise). Sensible defaults are provided for a new marking scheme.

Parameter Description
Feedback column width Character columns allocated for feedback comment cells.
Individual comments column width Character columns allocated for free-form per-student comment cells.
Marks columns width Width of columns containing numerical marks.
Student ID column width Width of the student ID column - not automatically calculated, since ID cells are a formula type (to preserve leading zeros).
Grade description height (pts) Extra row height given to grade description cells, in points.
Default sheet magnification (%) Zoom level for sheets used for entering grades. Set to 100 for the normal view.

Check the generated spreadsheet's layout before entering marks - it is far quicker to adjust a parameter and regenerate than to reformat every sheet by hand afterwards.

Creating the marking spreadsheet

Set the Passmark (used for fail-code prompts), then click Create marking spreadsheet. The workbook is always saved in the same folder as the marking scheme file, named after the marking scheme and cohort year - there's no separate filename to set. If the marking scheme has a problem that prevents generation (a zero-weighted assessment, an empty task list, and so on) this is explained in the message area at the bottom of the window rather than the spreadsheet being produced.

The workbook contains a Summary sheet, a Results sheet, and four sheets per assessment (Grades, Picking, Marks, Feedback). Marking is normally done entirely on each assessment's Picking sheet, then reviewed on Results - the other sheets exist so the marking scheme can be adjusted mid-way through marking and the calculations traced if needed. Named regions (e.g. __Assignment_name__, __RESULTS__) make it easy to jump straight to the right area of a large workbook from Excel's Name Box.

Appending a marks entry / order form

Once results are complete, the Marks entry report STR00009 field and its Find STR00009 report file button locate the student-records order form (a .csv export). Clicking Create marks entry spreadsheet reads the marking spreadsheet, adds a new sheet that reorders the results to match the order form and adds checksums, and writes the result as a separate file - the original marking spreadsheet is left untouched (see "Where the files go" below).

There's no need to click Create marking spreadsheet first if the STR00009 report is already available - if the marking spreadsheet doesn't exist yet, Create marks entry spreadsheet generates it automatically before adding the marks entry sheet. You can still click Create marking spreadsheet yourself first if you'd rather check it over before the marks entry sheet is added - either order works. If generation fails (or you cancel a warning it shows, e.g. about a default cohort year), the marks entry step is not attempted and a message explains why.

Where the files go - both are written to the same folder as the marking scheme (.mfax) file, named after the marking scheme and cohort year:

Workbook structure

The generated workbook has a Summary sheet, a Results sheet, and four sheets per assessment:
Results holds each assessment's overall result, weighted by its module contribution. Summary is similar, but also includes the task/question marks for any assessment with "exam" (case-insensitive) in its name - its "raw mark" for each assessment comes from Results, everything else from that assessment's Marks sheet.

Marking is normally done entirely on each assessment's Picking sheet, then reviewed on Results - the other sheets exist so the marking scheme can be adjusted part-way through marking and the calculation traced if needed. The quickest way to move around a large workbook is the named regions below, accessible from the cell reference box near the top-left of Excel's formula bar (View > Formula Bar if it's hidden).



Propagation of data between sheets



Results and Summary derive their data from each assessment's Marks sheet. The marks entry/order form (see "Appending a marks entry / order form" above) derives all of its data from Summary. A few points worth knowing if a mark needs adjusting directly in Excel rather than via the Picking sheet:
  1. Overriding an assessment mark on Results does not propagate to Summary - correct genuine errors on Picking or Marks instead and let the result propagate; deliberate adjustments to a whole assessment/exam are best made on Summary.
  2. Overriding the Raw mark on Results does propagate to Summary (overriding whatever task marks it was calculated from) - this is where to rescale an entire cohort's result for an assessment.
  3. Changing marks on an assessment's Marks sheet propagates to Results and Summary, as long as that value hasn't itself been overridden there - so this is where to rescale marks for a whole assessment, via the "Module Contribution %" column.
  4. Overriding an entry on the marks entry/order form sheet has no further effect - every column there is copied directly from Summary, with no calculations besides the checksum.

Non-numerical entries in exams

Cells with no mark allocated show as empty text, which propagates through the other sheets without taking part in any calculation. A mark that violates the exam rubric shows as ! n (n being the mark) - also propagated but excluded from calculations; enter it on the Picking sheet as -n.

Named regions

Each workbook has these named regions, mainly useful for jumping around a large workbook via Excel's cell reference box. Only __Assignment_name__ and __RESULTS__ are needed for day-to-day marking; the rest exist for reference.

Name Refers to
__RESULTS__ The Results sheet.
__Assignment_name__ The area of that assessment's Picking sheet a marker actually fills in.
Assignment_name_PICKING The full Picking sheet area for that assessment.
Assignment_name_MARKS That assessment's Marks sheet.
Assignment_name_FEEDBACK That assessment's Feedback sheet.
Assignment_name_Task_name A task's grade, mark and feedback on the Grades sheet.
Assignment_name_Task_name_GRADES A task's mark on the Grades sheet.

Adding a grade after the spreadsheet has been generated

A task's grades live in its Assignment_name_Task_name_GRADES named region. To insert one:
  1. Select the name, grade and feedback columns in the row below where the new grade should go.
  2. Insert > Cells, then "Shift cells down".
  3. Type the new grade into the empty row - it will appear in the Picking sheet's drop-downs.
  4. If this extends the mark range, adjust the "maximum available mark" cell on that assessment's Marks sheet too, or the new grade will push the task above 100%.
If the new grade doesn't appear in the Picking sheet's drop-down, the named region for that task has probably fallen out of date rather than become corrupted - fix it via Insert > Name > Define, find the two entries for that task, and extend the row number at the end of the "Refers to" box to cover the row(s) just added.

Adding a student after the spreadsheet has been generated

  1. Click the row number below where the new student should go, then Insert > Row.
  2. Copy an existing student's row and paste it into the new row.
  3. Edit the student ID, name and email to the new student's details.
Do this on every sheet the student has a result on. On the Results sheet, any assessment they haven't been added to yet will show #N/A - fill it with the appropriate value (usually 0).