Automatically marked question. The question is answered by entering values on an Excel spreadsheet.

The Microsoft Excel question type in Inspera Assessment can be either an automatically marked question (as described in this article) or a manually marked question. With this question, the Author can prepare a Microsoft Excel for the Web spreadsheet, which the candidates will edit. The Author can define the marking rules to automatically determine whether a cell contains the correct value or not.

This article explains the core elements of Microsoft Excel questions, details how to author them, and lists its known limitations.

Understanding the Microsoft Excel question type

The Microsft Excel question type offers a direct integration with Microsoft Excel for the Web, which it part of the O365 suite.

In this question, the Author can create a spreadsheet from scratch or use one they already own. This is the spreadsheet the candidates will edit and against which the marking rules the Author defines will be evaluated, for the automatic marking.

How to author a Microsoft Excel question

In order to author an automatically marked Microsoft Excel question, the Author needs to follow four steps:

1. Create a new question
2. Define the question text
3. Attach a spreadsheep
4. Set up marking rules

Create new question

1. Start by selecting the Author module > Questions.
2. Click Create new (we suggest using New Author).
3. Under Automatically marked, click Microsoft Excel.

Define Question Text

Similarly to the other question types offered by Inspera, the question text depicts the instructions the candidates should follow to answer the question. It can include text, images, tables, etc.

 We provide some Instructions for the Author as displayed in blue in the image. Please replace this with the question text as they will otherwise be shown to the candidates as part of the question text.

Attach a spreadsheet

The spreadsheet is the Excel file the Author provides to the candidate and that the candidates will edit in order to answer the question. There are two ways of attaching the spreadsheet:

1. Create template on browser
2. Via the Excel panel

Create template on browser

By clicking "open in Microsoft Excel for the web in a new tab", a new tab will be opened with an empty spreadsheet. The Author can edit the spreadsheet as they want. This is the spreadsheet the candidates will be asked to edit when taking the assessment. The spreadsheet is automatically saved after each edit and the Author can edit it as many times as they want.

Via the Excel panel

On the right sidebar, there is a new section called Excel Panel. Here, the Author can upload two documents: an existing template (Upload existing template) and/or a model answer sheet.

When uploading an existing template, the Author is prompted to select an excel file from their computer and upload it into the question. This is the spreadsheet that is going to be edited by the candidates when answering the question.

The model answer sheet is NOT used for automatically mark the question, in any way, it will mostly be used in the Manually marked version of the Microsoft Excel question type.

Set up marking rules

As this is an automatically marked question, the Author needs to define the marking rules against which the candidates' spreadsheets will be evaluated. This is done under the Marks section on the right sidebar.

In this section the Author can define one or more marking rules. Each marking rule consists of:

• Expected Cell: the spreadsheet cell whose value will be evaluated, e.g., C34;
• Expected Answer: the value that should be in the Expected Cell for the answer to be considered correct. If the candidate uses a formula, the calculated value will be used, not the formula itself;
• Marks per correct answer: how many marks this correct answer is worth (it can be a decimal value)

In order to access the Expected Answer and Marks per correct answer the Author must scroll horizontally.

Clicking on "Add marking rule" add a new line for a new marking rule to be configured.

The Author can also define that case sensitivity or white spaces should be ignored when evaluating the answer. This is only applied to the Expected Answer. For example, assuming the Expected Answer is Dog: if ignore case sensitivity is enabled, all of these answers will be considered correct: Dog, DOg, DOG, DoG, dOG, doG, dOg, doG, dog; If ignore white spaces is enabled, then D og will also be considered correct.

Candidate Experience

When the candidate is taking the test, they will have to open the spreadsheet on a new tab by clicking on "Open in Microsoft Excel for the web". Any changes they do will be automatically saved but there will be no visual indication that happened.

Known Limitations

• Currently, only the first work sheet of the spreadsheet is supported, as such if Expected Cell is C34 the C34 cell of the first work sheet will be used to evaluate the answer. We do not support Sheet2!C34 in the Expected Cell
• It is not possible to block the cells of the spreadsheet the Author prepared, the candidates will be able to edit the whole spreadsheet. The original one is always stored and can be accessed both in Author and in Grade
• It's not possible to compare against a formula, the Expected Answer must contain the value the formula would calculate. If the candidate uses a formula or does the calculations by hand, the end result will be the same
• When the candidate opens the spreadsheet, it will open on a new tab, not embedded in the question view. This is a constraint imposed by Microsoft
• Any changes a candidate does on a spreadsheet will be automatically saved but there will be no visual indication that happened.
• Offline support: the changes are done within the Microsoft Excel software, we do not have a way of accessing them if the candidate goes offline. Once they are online again, we will save all their changes.
• This question type is not compaticle with SEB and it should only be used either in an Open Browser or IIB v2