Excel tasks

In an Excel task, students download the starter workbook (if there is one), do the task in Excel and upload an .xlsx workbook. Your grading rules grade it automatically: each rule checks one thing – a value, a formula, formatting, a chart – and gives points.

An Excel task is created like any other problem, with Type set to Excel task (see New problem). You write its statement in the same editor; it needs no input, output or sample tests.

The grading rules page

The problem’s Excel grading card shows the number of rules and points, the upload limit and the starter file. Rules (for non-editors, View rules) opens the Grading rules page; the address is /library/problems/<problem>/rules.

Every change on this page goes into the task’s draft. The published version and the uploads already checked keep their results until you publish a new version (see Drafts and publishing). The top of the page says which version is being prepared; Open the task to publish leads to the problem page. Others can see the rules but not change them.

Starter workbook

The workbook students start from. They download it from the task page.

• Upload .xlsx – only .xlsx without macros, up to 9 MB. The file is checked like students’ uploads.
• Replace, Download, Remove. After removing it, students of the next published version start from an empty workbook; published versions keep theirs.

The starter workbook is optional: without it, students start from an empty workbook.

Uploads per student

Uploads per student: Unlimited, 1 (no re-upload), 2, 3, 5 or 10. Students see how many uploads are left; the best result counts. A file that isn’t accepted (not .xlsx, with macros, encrypted or damaged) doesn’t use up an upload.

Rules

Screenshot to comeThe “New rule” dialog: name, points, what to check, sheet, cell or range, and notes for the studentTeacher account, the rules page of their own Excel task, “Add rule” open with “Formula result” chosenstaff/library/excel-rule-dialog

Adding a rule

1. Click Add rule.
2. Enter a Name (for example “Total in B10”) and the Points (a whole number 0–100, 1 by default).
3. Choose What to check (see the table below). A short explanation of the chosen check appears under the list.
4. Give the Sheet. Empty means the first sheet; for chart and error checks, empty means every sheet.
5. Give the Cell or range (for example B10 or B2:B20), columns (B:D) or rows (1:3), depending on the check.
6. Fill in the check’s parameters.
7. Optionally write a Note when met and a Note when not met – the student sees it with the result.
8. Click Save rule.

Each row of the rule list has a menu: Edit, Move up, Move down, Delete. A deleted rule is removed from the draft; uploads already checked keep their results.

What students see

Even before uploading, students see each rule’s name and points; after a check, whether it is Met, Partly met, Not met or Could not verify, with an explanation and your note. They never see the expected values.

Caution

Don’t put the answer in a rule’s name – students see it in advance.

How points are counted

• Each rule is checked on its own.
• A rule on a range gives part of the points when part of the cells (columns, rows) pass: the points follow the share of passing cells, rounded to a whole number. Full points need every cell to pass.
• An upload’s score is the sum of its rules’ points. Full points mean the task is solved.
• Formula results are taken as Excel saved them. When a file has no saved result for a formula (it was written by a program that doesn’t calculate), the rule is Could not verify: it gives no points, and the student is asked to open the file, recalculate and save it.
• A task has at most 60 rules; one rule covers at most 20,000 cells.

A task can be published only with at least one rule worth points.

Check types

Group	Check	What it checks
Values and formulas	Cell value	A cell (or each cell of a range) holds the expected value, typed or calculated: a number (with an Allowed difference), text (optionally Match upper and lower case), TRUE/FALSE, a date or empty.
	Formula in cell	The cells contain formulas, not typed values. A formula that is only a number (e.g. =1250) can count as a typed value.
	Function used	The formulas use a function: Any of them or All of them from a list (e.g. SUMIF, SUMIFS). Forbidden functions can be given.
	Formula argument	The formula uses a range or criterion, e.g. the first argument of SUMIF is B2:B20. The function and argument number are optional.
	Formula result	The cells contain formulas whose calculated result is the expected value.
Formatting	Formatting	Bold, italic, underline, wrap text, merged cells, text and fill colour (is / is not, none / white), alignment, number format and decimal places. Only the properties you set are checked.
	Borders	All borders, outside borders or one edge, optionally of a line style.
	Column width	Columns are at least and/or at most this wide (in characters).
	Row height	Rows are at least and/or at most this high (in points).
	Conditional formatting	Conditional formatting covers the cells, optionally of a type.
Data	Data type	The cells hold numbers, text, dates, TRUE/FALSE, are empty or not empty.
	Data validation	Data validation (Data › Data Validation) covers the cells, optionally of a type or with a list of values.
	Filter	A filter is set on the data (the range is optional).
	Sorting	A column’s values are in Ascending or Descending order.
	Named range	A name is defined (Formulas › Define Name), optionally for a range.
Charts	Chart	The sheet (or workbook) has at least this many charts.
	Chart type	A chart is of the chosen type: column, bar, line, pie, doughnut, area, scatter, bubble, radar, histogram, Pareto, waterfall, box and whisker, funnel, sunburst, treemap.
	Chart title	A chart has this title – Exactly or Contains.
	Axis title	The horizontal, vertical or any chart axis has this title.
	Chart data	A chart uses this data range.
Errors and sheets	No error values	No cell shows #DIV/0!, #REF!, #VALUE!, #NAME?, #N/A… (with none selected, every error value counts).
	Sheet exists	The workbook has a sheet with this name.

After publishing

After a new version is published, uploads already checked keep their results. If you fixed a wrong rule, earlier uploads can be checked again on the course’s Excel results page (Check again…), which also shows each student’s results, rule statistics and the export (see Excel task results). How students do the task is on the students’ page. Excel tasks can’t be tested in the code editor.