From spreadsheet to assessment software: what does a blank answer mean?

Before commissioning software for your assessment method, agree what an unanswered question means. Does it reduce the score, disappear from the calculation, or prevent a final result? That decision belongs to the person responsible for the method. It should be settled before a developer translates the workbook.

A useful starting point is a short set of invented inputs with agreed results. It makes the calculation, the completion status and the conditions for releasing a report visible together.

Two answers, three possible outcomes

Consider an illustrative assessment with three equally weighted questions, each scored from zero to four. The first answer is 4, the second is 2, and the third is unanswered.

If you average only the two answers, the result is (4 + 2) ÷ 2 = 3. If you replace the missing answer with zero and average all three, the result is (4 + 2 + 0) ÷ 3 = 2. You could also decide that no final score is available until all required questions are answered.

Excel's AVERAGE function ignores genuinely empty cells in a referenced range and includes numeric zero. So AVERAGE(A1:A3), with numeric 4, numeric 2 and an empty third cell, returns 3; entering zero in that cell changes the result to 2. [1]

These outcomes express different rules. A software migration needs an explicit choice. If you permit the provisional result of 3, show its coverage alongside it: two of three questions answered. A complete assessment with the same score should remain distinguishable.

Give each state its own meaning

We recommend defining at least the following states for each question. Add others only when your method requires them.

1. Unanswered: no assessment has been recorded. Decide whether a provisional total is useful and whether a final report is blocked.
2. Assessed at zero: a valid answer at the bottom of the defined scale. Preserve it as an answer, including any evidence your method requires.
3. Not applicable: the question has been explicitly excluded for an agreed reason. Define who can approve that exclusion and how it affects the denominator.
4. Awaiting review: an answer exists, but the required professional review is incomplete. Specify whether it contributes to a provisional result and who can release it.

Record exclusions separately from unanswered questions. Otherwise, “two of three answered” leaves the reader guessing whether the third question was forgotten or deliberately removed. For a weighted method, also write down what happens to the excluded question's weight; the equal-weight example above does not settle that decision.

Apply the same rules to imports. The W3C specification for tabular metadata distinguishes markers for null values from defaults applied to empty strings. It offers a vocabulary for describing these choices; it does not make an arbitrary importer follow them. [4] Ask your implementation team to document which incoming values become unanswered, zero or an explicit exclusion, and to reject ambiguous mappings until they are resolved.

Agree when rounding happens

A second illustrative case concerns a score of 2.96 and a category that starts at 3. Displaying one decimal place produces 3.0. Comparing the underlying 2.96 with the threshold still places it below 3. Rounding to one decimal before comparing would place it at the threshold.

For Excel for the web, Microsoft documents that number formatting changes the display rather than the underlying calculation value; ROUND performs a rounding operation. [2] [3] Neither operation tells you which category your methodology intends. Specify the order: calculate, round if required, compare, then display. Make the report explain the result consistently with that order.

Turn the decisions into reference cases

Before building screens, ask the method owner and implementation team to agree a compact set of examples. For each, record the inputs, applicability decisions, expected score, completeness, review status and whether a final report may be released.

1. A normal, fully answered assessment with an independently calculated expected result.
2. The same assessment with one answer missing, then with that answer explicitly set to zero.
3. An approved exclusion, including its effect on the denominator or weights.
4. No eligible answers remaining: define an explicit no-result state rather than silently assigning zero.
5. Values below, at and above a category boundary, plus a value whose displayed rounding reaches the boundary.
6. An out-of-range value, ambiguous imported text and an answer awaiting review: state the validation or release behaviour expected in each case.

Use invented data that isolates each rule. A large realistic example can make a discrepancy harder to trace. Keep both the unrounded calculation and the displayed result where the method distinguishes them.

Reconcile the method, not just the workbook

Run these cases through the existing workbook and the new application. Investigate each difference. The workbook is evidence of current behaviour; the person responsible for the method still needs to confirm that behaviour is intended.

If a workbook rule is wrong, document the correction and approve the new expected result. Record which method version produced an assessment, and decide explicitly whether historical assessments stay unchanged or are recalculated. Keep the agreed cases so that later changes can be checked against them.

For your next scoping discussion, bring three things: the workbook, a one-page rule sheet and these approved examples. Together they give the implementation team a concrete definition of what must remain consistent—and where the method is deliberately changing.

Sources

[1] Microsoft Support: AVERAGE function. Referenced ranges, empty cells and zero values.

[2] Microsoft Support: Format numbers. Display formatting and calculation values.

[3] Microsoft Support: ROUND function. Rounding to a specified number of digits.

[4] W3C: Metadata Vocabulary for Tabular Data, inherited properties. Null markers and default values.

Sources checked on 30 September 2026. The numerical scenarios are invented; the implementation recommendations are editorial guidance, not a prescribed assessment standard.