excel-formula-emission

name	excel-formula-emission
description	Use when a task's answer is an .xlsx workbook that will be graded by a script, especially when the grader reads cells with openpyxl or converts the sheet to CSV. Fires on "write Excel formulas", "keep the formulas in the file", "no hardcoded values", "use openpyxl / xlsxwriter", "the cell must start with =", "recalculate the workbook", or a checker that reads a specific cell address. Covers the choice between emitting a live `=` formula and a literal value, why openpyxl formulas read back as None, the `_xlfn.` prefix, cross-sheet quoting, and the file-integrity rules graders enforce.
metadata	{"short-description":"Emit `=` formulas vs literals by reading what the checker reads; recalc so values cache; `_xlfn.` prefix; quoted cross-sheet refs; preserve the file"}

excel-formula-emission

A spreadsheet answer is graded two different ways: by the text in a cell, or by the value in a cell. Read the checker first and match the mode it uses; guessing wrong scores zero even when the arithmetic is perfect.

When to use this skill

• The deliverable is an .xlsx file that a script — not a human — will open and read.
• A checker asserts a cell's text: cell.value.startswith("="), or "IF(" in cell.value, "SUMPRODUCT(", "TREND(", "ROUND(".
• A checker reloads with load_workbook(path, data_only=True), or exports the sheet to CSV, and so needs a computed number sitting in the cell.
• You are writing formulas through openpyxl or xlsxwriter and a cell reads back as None, or shows #NAME? / #REF! / #DIV/0!.
• You were given a workbook to edit and must hand back the same file with its sheets, styles, column widths, and untouched cells intact.
• The formula needs a function added after Excel 2007 (STDEV.S, PERCENTILE.INC, XLOOKUP), a reference to another sheet, or a spilling array function such as TREND.
• Not for: analysing spreadsheet data when the answer is a number, a CSV, or prose. If nothing is written back into a cell, none of this applies.

Procedure

1. Read the checker before writing anything. Extract the exact cell addresses, the exact sheet names, and which of the two grading modes it uses. Every other decision follows from that.
2. Choose formula string or literal value from the Grading modes table. When a formula is required and its value is also checked, emit the formula and then recalculate so a cached value is stored alongside it.
3. Open the provided workbook with data_only=False (the default) and edit it in place. Do not build a fresh workbook.
4. Write the cells, applying the _xlfn. prefix rule and the cross-sheet quoting rule below. Write to the exact address and sheet the task names — graders read fixed coordinates.
5. Recalculate the file so every formula cell carries a cached value.
6. Scan the graded range for error cells: #REF! #DIV/0! #VALUE! #NAME? #NUM! #N/A. One error cell can fail a whole-workbook check. #NAME? almost always means a missing _xlfn. prefix or a misspelled function; #DIV/0! means guard the denominator.
7. Verify both views of the saved file with the assertions in Worked example.

Grading modes: formula string or literal value?

The grader does…	The cell must hold…
assert cell.value.startswith("="), or assert "IF(" in cell.value / "SUMPRODUCT(" / "TREND("	an Excel formula string — a literal number fails outright
assert cell.value == 4639 (exact) or sums cells with sum(...)	a number — a formula string breaks == and sum()
reads a value with a tolerance, from the xlsx or an exported CSV	either a number, or a formula that has a cached value

When the grader reads stored values and sums cells directly (a data-recovery / fill-the-blanks task), write the computed number into each cell, not a formula.

openpyxl formulas have no cached value

openpyxl writes a formula as a string and stores no computed result. A grader that reloads with load_workbook(path, data_only=True) then reads None for every formula cell. Fix by recalculating after you write — a headless LibreOffice round-trip bakes the cached values in:

# recalculate into a scratch dir beside the workbook, never through a shared /tmp path
mkdir -p recalc_out && soffice --headless --calc --convert-to xlsx --outdir recalc_out output.xlsx \
  && cp recalc_out/output.xlsx output.xlsx

Check the exit status and re-open the file afterwards; a silent failure here looks identical to a successful recalc until the grader reads None. Only ever write back over your own output file — if the task provided a workbook and names a separate output path, convert to the output path and leave the provided file untouched.

If neither soffice nor ssconvert is installed, do not install it. Fall back to the grading mode: when the check reads the cell text, skip recalculation entirely; when it reads the value, write the computed literal instead of a formula. Many graders instead export to CSV with gnumeric and read that — so the formulas must be evaluable by gnumeric/LibreOffice, not just by Excel:

ssconvert -S output.xlsx sheet.csv       # writes sheet.csv.0, sheet.csv.1, … one per sheet

The _xlfn. prefix for modern functions

Functions added after Excel 2007 are stored in the file with an _xlfn. prefix. With openpyxl you must write the prefix yourself or the function name corrupts and the cell errors with #NAME?:

ws["D5"] = "=_xlfn.STDEV.S(C3:C5)"        # not "=STDEV.S(...)"
ws["H5"] = "=_xlfn.PERCENTILE.INC(A1:A6,0.25)"
ws["B5"] = "=_xlfn.XLOOKUP(...)"

Legacy functions need no prefix: SUM, AVERAGE, MIN, MAX, MEDIAN, STDEV, LN, EXP, SQRT, IF, ROUND, INDEX, MATCH, VLOOKUP, HLOOKUP, SUMPRODUCT, SUMXMY2, SUMSQ, TREND, POWER, AVERAGE. Prefer a legacy equivalent when one exists (STDEV instead of STDEV.S, PERCENTILE instead of PERCENTILE.INC) to sidestep the prefix entirely. xlsxwriter's write_formula() applies the prefix automatically, so with xlsxwriter write the plain name.

Cross-sheet references and array formulas

Reference another sheet with =Sheet!A1. If the sheet name contains a space or punctuation, single-quote it — miss the quotes and it errors:

ws["C4"] = "='Gold price'!D430"
ws["D31"] = "='SUT calc'!C46"
ws["C23"] = "='Total Reserves'!B5"

For a spilling function such as TREND, either write the plain string and recalc, or set an array formula explicitly:

from openpyxl.worksheet.formula import ArrayFormula
ws["G58"] = ArrayFormula("G58", "=TREND($G$44:$G$57,$C$44:$C$57,C58)")

File-integrity rules graders enforce

• Never load_workbook(path, data_only=True) then save() — that discards every formula permanently. Load with the default (data_only=False) to edit and preserve formulas; open a separate data_only=True handle only to read values.
• Edit the provided file in place. Load the given workbook, change the target cells, save to the required path. Do not create a fresh workbook from scratch — you lose the sheets, styles, and column widths the grader expects. Do not add or remove sheets it does not expect.
• Keep the extension .xlsx; do not write .xlsm or add VBA. Graders reject any vbaProject / .bin part and non-.xlsx extensions.

Worked example

A task says: put the 12-month volatility in Answer!C13 as a live formula, and the checker both asserts the cell text starts with = and compares the value to 4.82 within a tolerance. That is the third row of the grading table plus the first — emit the formula, recalculate, then confirm both views of the saved file agree:

from openpyxl import load_workbook
f = load_workbook("output.xlsx", data_only=False)["Answer"]   # formulas present?
v = load_workbook("output.xlsx", data_only=True)["Answer"]    # cached values present?
assert str(f["C13"].value).startswith("="), f["C13"].value
assert isinstance(v["C13"].value, (int, float)), "recalculate so values cache"

If the second assertion fires, the recalculation step did not run or did not succeed; re-run it before finishing. If the first fires, a literal was written where the checker reads text.

References

None. Everything here is universal openpyxl / OOXML / LibreOffice / gnumeric mechanics, independent of any dataset, and needs no bundled file. The only per-task details are which cell addresses to write and which of the two grading modes applies — both come from reading the task's checker.