Scoring Formulas

A checklist question is marked item by item, and a formula turns those marks into the single number Forefront stores. The guided Builder covers the common case — so many points per item in each state — and a Custom formula covers everything else.

The syntax is deliberately Excel-flavoured: if you write spreadsheet formulas, you already know most of it. IF, IFS, AND, OR, NOT, MIN, MAX, SUM, ROUND, FLOOR and CEILING behave as you’d expect, and a handful of Forefront functions describe the marks themselves.

Where to write one

1. In the assessment editor, select the question and set its Question Type to Checklist (see Question Types).
2. Define the Response States and Item Groups first — formulas refer to them by name, so they need to exist.
3. Under Scoring, choose Custom formula.

Switching from Builder to Custom formula seeds the field with the builder’s own formula, which is both a working starting point and a good way to see what the builder was doing.

As you type, the field completes function names, and inside a "…" argument it completes your response-state labels and group ids. A formula that can’t be compiled — or that can’t produce a number — is reported under the field and won’t be saved.

Syntax basics

A formula is just an expression, and it must work out to a number.

SUM(COUNT("Correct"), 0.5 * COUNT("Self-corrected"))

• No leading =. Unlike Excel, you write the expression on its own.
• Numbers are written plainly: 4, 0.5, -2.
• Text — a response-state label or a group id — goes in double quotes: COUNT("Correct"). Text is matched case-insensitively, so "correct" finds a state labelled Correct.
• TRUE and FALSE are available, mostly as the catch-all in IFS.
• Whitespace and line breaks are ignored; use the field’s expand toggle for a long formula.

Operators

Precedence runs the usual way — * / % before + -, both before the comparisons — and parentheses override it. A leading - binds tightest.

Two things to watch:

• % is a remainder operator. 50% does not mean “50 percent” here; write 0.5, or divide.
• A comparison produces true/false, not a number. SCORED() >= 4 on its own is not a valid scoring formula — wrap it: IF(SCORED() >= 4, 1, 0).

Referring to the marks

Three things a formula can talk about:

• Response states, by their label: COUNT("Correct").
• Item groups, by their id: COUNTIN("<group id>", "Correct"). Ids aren’t meant to be typed — put the caret inside the quotes and pick the group from the completion list.
• Individual items, by position: W1, W2, W3… numbered from 1 across all groups in order. An item reference gives you the label of the state that item is marked with, or empty if it’s unmarked, so it’s normally compared: IF(W1 = "Correct", 2, 0).

Unmarked items are “not attempted”: they’re counted by TOTAL() but not by ATTEMPTED(), SCORED() or any COUNT.

Retired items are counted by nothing. An item removed from the list is retired rather than deleted, so the marks already on it are kept — but it drops out of TOTAL(), ATTEMPTED(), COUNT(), SCORED() and WHOLEGROUPS(), and positional references skip it. The score is of the question as it stands.

Worked examples

Count the correct items — the default, and the whole formula:

COUNT("Correct")

Partial credit for a second state — self-corrections worth half:

SUM(COUNT("Correct"), 0.5 * COUNT("Self-corrected"))

Percent accuracy, rounded to a whole number. TOTAL() can’t be zero once the question has items, but guarding it costs nothing and a division by zero is an error:

IF(TOTAL() = 0, 0, ROUND(100 * SCORED() / TOTAL(), 0))

Accuracy over what was attempted, which is what a running record usually wants — a passage stopped early shouldn’t read as a failure:

IF(ATTEMPTED() = 0, 0, ROUND(100 * SCORED() / ATTEMPTED(), 0))

A mastery flag — 1 when the student clears the bar, 0 when they don’t:

IF(COUNT("Correct") >= 4, 1, 0)

Whole words read (DIBELS-style nonsense-word fluency, one group per word): the number of groups in which every sound was correct.

WHOLEGROUPS("Correct")

Sounds plus a bonus for whole words — the two scores that assessment traditionally reports, combined:

SUM(COUNT("Correct"), 2 * WHOLEGROUPS("Correct"))

Score one group only, or weight one group differently:

SUM(COUNTIN("<first group id>", "Correct"), 2 * COUNTIN("<second group id>", "Correct"))

Bands instead of a raw count. IFS takes condition/value pairs and returns the first match; finish with TRUE so there is always a match:

IFS(SCORED() >= 20, 3, SCORED() >= 12, 2, SCORED() >= 5, 1, TRUE, 0)

Cap a score without an IF:

MIN(COUNT("Correct"), 10)

Weight the first item — a title read correctly, say:

SUM(IF(W1 = "Correct", 2, 0), COUNT("Correct") - IF(W1 = "Correct", 1, 0))

Function reference

Checklist functions

These describe the marks on the question being scored.

SCORED()

No arguments. The count of items whose state has the Scores toggle on. Unlike COUNT, it doesn’t name a state, so adding another scoring state later doesn’t need a formula edit.

SCORED()

COUNT(state)

The number of items marked with one specific state, anywhere in the question. state is the state’s label, matched case-insensitively.

COUNT("Correct")
COUNT("Self-corrected")

Note that this names a state explicitly, so renaming that state in the editor leaves the formula looking for a label that no longer exists and it will count 0. Rename deliberately, and re-check any custom formula afterwards.

COUNTIN(group, state)

The number of items in one group marked with one state. group is the group’s id — pick it from the completion list inside the quotes rather than typing it. Because it’s an id and not the label, renaming a group is safe.

COUNTIN("b6f0…", "Correct")

WHOLEGROUPS(state)

The number of groups in which every item is marked with that state. A group with no items in it never counts. This is the “whole words read” measure: one group per word, every sound correct.

WHOLEGROUPS("Correct")

TOTAL()

No arguments. The total number of items on the question — the denominator for a percentage of the whole task.

ROUND(100 * SCORED() / TOTAL(), 0)

ATTEMPTED()

No arguments. The number of items carrying any mark at all. Use it instead of TOTAL() when a student who stopped part-way should be scored on what they actually did.

Wn — item references

W1 is the first item, W2 the second, and so on, numbered straight through every group in the order the groups are listed — counting the items a teacher can actually see, so retired items are skipped. Retiring a word therefore shifts what W3 means; renaming or adding one doesn’t. The value is the label of the state that item holds, or empty when the item is unmarked, so compare it rather than doing arithmetic on it.

IF(W3 = "Correct", 1, 0)
IF(W1 <> "Correct", 0, 1)

Excel-style functions

IF(test, then, else)

All three arguments are required — there’s no one-armed IF. Give the else branch explicitly, usually 0.

IF(COUNT("Correct") >= 4, 1, 0)

IFS(test1, value1, test2, value2, …)

Condition/value pairs, evaluated in order; the first true condition’s value is the result. If nothing matches, the formula fails and the question can’t be scored, so end with a TRUE catch-all.

IFS(SCORED() >= 20, 3, SCORED() >= 12, 2, TRUE, 0)

AND(…), OR(…), NOT(…)

Combine conditions inside an IF or IFS.

IF(AND(W1 = "Correct", W2 = "Correct"), 1, 0)
IF(OR(COUNT("Correct") >= 8, WHOLEGROUPS("Correct") >= 2), 1, 0)

MIN(…), MAX(…), SUM(…)

Take individual numbers — there are no cell ranges here, so nothing like SUM(A1:A10). MIN and MAX are the tidy way to cap or floor a score.

MIN(COUNT("Correct"), 10)
MAX(SCORED() - 2, 0)
SUM(COUNT("Correct"), 0.5 * COUNT("Self-corrected"))

ROUND(number, digits)

Rounds to a number of decimal places; 0 gives whole numbers. Both arguments are required (Excel lets you omit the second; here it’s explicit). Halves round away from zero, matching Excel.

ROUND(100 * SCORED() / TOTAL(), 0)
ROUND(SCORED() / 3, 1)

FLOOR(number, [significance]) and CEILING(number, [significance])

Round down / up to a multiple of significance, which defaults to 1. A significance of 0 is an error.

FLOOR(SCORED() / 2)
CEILING(SCORED(), 5)

The score range

Forefront works out a checklist question’s achievable range from the formula itself, by evaluating it across the ways the items could be marked, and shows the result under the configuration — “Scores 0 – 24”. That range is what Proficiency Alignment and Score Mask then lay out over, so you don’t set Min/Max by hand.

For a very large question the number of combinations is too big to work through exhaustively, and Forefront falls back to bounding the range from the extreme cases. It says so: the readout gains "(approximate)". The score is unaffected — only the stated top and bottom of the range are an estimate — but it’s worth a sanity check before you align proficiency bands against it.

When a formula fails

• While authoring, the message under the field is the exact problem — unknown function COUNTIF, expected ')', IF expects 3 argument(s), got 2. An invalid formula isn’t saved.
• While scoring, a formula that errors on a particular set of marks (a division by zero, an IFS that matched nothing) means that entry gets no score. The marks are still stored, so fixing the formula and re-saving is enough to bring the scores back.
• Scores are computed by the server when the marks are saved, using this same language, so what you preview in the editor is what the gradebook stores.

See also

• Question Types — the checklist type’s other options: response states, arrangements and groups.
• Assessment Spreadsheet — marking a checklist question as a teacher.