Calculated columns and formulas
A calculated column is worked out by MarkBook from other columns. Teachers do not type in it. Typical uses: Value Added (report grade minus target grade), a weighted average of three tests, or the best of four quizzes. The result appears in the grid, the Summary, exports, reports and the parent view, and is always up to date.
Where: Configuration → Assessment → Markbook Templates → open a template → Template settings. A calculated column shows a formula icon. Select it to open the formula builder.
Who can change this
A manager, or a person whose role includes Markbook templates. Defining the column itself needs Value types. See Column Library (Value Types).
How a calculated column works
- It is a column kind with the data type Calculated, such as VA Grade. You switch it on in a zone of the template, usually Reporting period.
- Its formula names other column kinds, for example
RGandTG. For each student, MarkBook looks up those columns, does the sum and shows the answer. - Plain arithmetic needs every value. In
A - B,A * 0.4 + B * 0.6orsum(...), if any column is blank for that student, the result is blank. A difference or a total with a part missing has no answer. - Averages use the marks that exist.
avg()(alsomean),min()andmax(), and the Weighted tab, leave a missing mark out and work on the marks the student has.avg(N1, N2, N3, N4)with N3 empty is (N1 + N2 + N4) / 3. If every mark they name is missing, the result is blank. - MarkBook never guesses a zero. To average marks where some may be missing, write
avg(...)or use the Weighted tab, not(A + B + C) / 3. - Without a formula, the column stays empty in the grid.
- Calculated cells are read-only. A teacher who hovers over one sees "Calculated automatically — this value comes from a formula and cannot be typed."
Which value does a name refer to?
A name in a formula stands for a column kind. For a given student MarkBook reads:
- the column in the same checkpoint as the calculated cell, if there is one;
- otherwise the column set once for the year (a target such as TG).
What it reads from the cell depends on the column:
| Column holds | The formula uses |
|---|---|
| A grade (grade label) | The grade's points from the attainment scale. A = 7 points counts as 7. |
| A judgement | The value's number (its weight). |
| A number | The number as typed. |
If a column has no points or number, the cell cannot be calculated. This is a common reason for a blank result.
The three tabs
The builder has three tabs. Pick the simplest one that does the job.
Arithmetic
A short chain of columns joined by Add, Subtract, Multiply or Divide. You choose columns from a list. Use Add another value type to extend the chain.
Arithmetic has no operator priority
The chain is worked out strictly left to right. A + B × C means (A + B) × C. If you need multiplication first, use the Advanced tab.
Reset to default returns the column to its standard formula.
Weighted
Four ready-made types:
| Type | What it does |
|---|---|
| Weighted average | Averages the columns, each with a Weight. |
| Drop lowest, then average | Throws away the lowest result or results (you choose how many in Drop), then averages the rest. |
| Lowest (min) | The lowest of the columns. |
| Highest (max) | The highest of the columns. |
Weights are relative: they are normalised for you and do not have to add up to 100. A column with Weight 50 beside two with 25 each counts for half. A Share column shows each value's effective share.
A missing mark is left out. The weighted average divides by the weights of the marks that are present. Example: tests 50%, quizzes 30%, homework 20%. If homework is missing, tests count 50/80 and quizzes 30/80. Drop lowest never drops the last mark that remains. If every column is missing, the cell stays empty.
The Weighted tab has a Decimals field (0 to 4). The result shows as a number with those decimals. A formula saved before this field existed shows two decimals.
Rules: a weighted average needs weights that add up to more than zero. Drop lowest needs at least two columns, and Drop must be smaller than the number of columns.
Advanced
A formula typed as text. Use it for brackets, priority, functions or decimals.
Syntax
| Element | Rule |
|---|---|
| Numbers | 5, 0.25 |
| Operators | +, -, *, /. The glyphs ×, ÷, − also work. * and / come before + and -. |
| Brackets | ( … ) group and set the order. |
| Column names | The column's short label, or its code, or its name, in any case. If the name has spaces or punctuation, put it in square brackets: [End of Year Grade]. |
| Functions | name(argument, argument, …) |
| Limits | 500 characters, 32 column references, 16 arguments to a function, 16 levels of brackets. |
Functions
| Function | Meaning | Example |
|---|---|---|
avg(a, b, …) (also mean) | Average of the marks that exist. A missing mark is left out. | avg(RG, EX) |
sum(a, b, …) | Total. Needs every value. | sum(T1, T2, T3) |
min(a, b, …) | Lowest of the marks that exist. | min(T1, T2) |
max(a, b, …) | Highest of the marks that exist. | max(T1, T2) |
round(x) or round(x, n) | Round to n decimals (0 to 4). | round(avg(RG, EX), 1) |
floor(x) | Round down. | floor(RG) |
ceil(x) | Round up. | ceil(RG) |
abs(x) | Distance from zero. | abs(RG - TG) |
clamp(x, low, high) | Keep x within a range. | clamp(RG - TG, -3, 3) |
AGGREGATE() (a school-wide average across subjects) is not supported. MarkBook says so if you type it.
Output
Under the formula you choose how the answer looks:
- Show as: Number, or Signed (+2 / −1). Signed is an Advanced-tab option. It shows whole numbers with a plus sign for positive values, the way value added is normally written.
- Decimals: 0 to 4.
Try it
Open Preview with test values, type a value for each column in the formula, and read the Result. Leave a box empty to see how a missing mark is handled. The preview uses the same rules as the grid. Test values are not saved. The editor flags mistakes as you type: unknown column ("No column matches that name"), a missing bracket ("Check the brackets — one is missing"), wrong number of arguments.
Worked examples
Value added = current grade − target
On the grade scale A* = 8, A = 7, B = 6, C = 5.
Formula (Arithmetic: RG Subtract TG, or Advanced):
RG - TGOutput: Signed.
| Student | TG | RG | VA |
|---|---|---|---|
| Maria | B (6) | A (7) | +1 |
| Andrei | A (7) | A (7) | 0 |
| Elena | A (7) | C (5) | -2 |
| Ioan | B (6) | (blank) | (blank) |
Ioan has no report grade yet, so his value added stays blank. See Conditional Colours to colour it red below target and green above.
Weighted average of three tests
Three number columns: T1 (Test 1), T2 (Test 2), T3 (Test 3). Test 3 is the end of unit exam and counts for half.
Advanced tab:
round(T1 * 0.2 + T2 * 0.3 + T3 * 0.5, 1)Or, on the Weighted tab, type Weighted average with weights 20, 30 and 50.
| T1 | T2 | T3 | Result |
|---|---|---|---|
| 60 | 70 | 90 | 60×0.2 + 70×0.3 + 90×0.5 = 78.0 |
On the Weighted tab, set Decimals (0 to 4) for the result. If one test is missing, the average re-divides by the weights of the tests that are present.
Best three of four quizzes
Weighted tab, Drop lowest, then average, Drop: 1, with columns Q1, Q2, Q3, Q4.
Quizzes of 6, 9, 7 and 8 drop the 6 and average 9, 7 and 8 to give 8.
Average of two columns, rounded
round(avg(RG, EX), 1)RG = 6 points, EX = 7 points gives 6.5.
Keep a result inside a range
clamp(RG - TG, -3, 3)A student 5 points above target shows as +3.
Rules and limits
- No loops. A formula cannot use its own column, and two calculated columns cannot refer to each other ("These formulas reference each other in a loop").
- Columns must be on the template. A formula can only use columns switched on in the same template. The builder lists those only.
- Two columns with the same short label are ambiguous: "Two value types share that short label. Rename one, or pick it from the list."
- Parents. A family sees a calculated value only if it is allowed to see the columns it is worked out from.
- Reports. A draft report always reflects the current marks. A published report keeps the values it showed when it was published.