Test an AI generated spreadsheet formula before using the result

Test an AI generated spreadsheet formula with independently known answers before using it in a business decision. Check ordinary cases, boundaries, missing data and the way references behave when copied. A formula that runs without an error can still calculate the wrong business rule.

This guide is for US teams using spreadsheets for internal operational work. ICAEW's 2024 spreadsheet principles recommend testing and review proportionate to workbook risk, including peer review. They are professional guidance, not US legislation. NIST's generative AI profile also supports treating generated output as something to evaluate in context. ICAEW twenty principles for good spreadsheet practice NIST Generative Artificial Intelligence Profile

Write the rule in plain language before requesting a formula. Build expected answers independently. Review references and data types as well as the displayed result. Keep an approved workbook version and seek specialist validation for consequential financial, regulatory or safety uses.

State the inputs, units, permitted values and intended output. Explain what should happen when an input is missing, zero, negative or outside the allowed range. If the business rule is unsettled, a more elaborate prompt will not resolve it reliably.

Use fictional sample data while developing the formula where possible. Do not upload a complete customer or financial workbook merely to explain three columns. Record relevant spreadsheet software, locale and function availability so that date handling and separators can be checked in the actual environment.

Calculate a handful of examples manually or through an independent trusted method. Include a normal case and cases that expose the rule's limits. Keep the expected result separate from the AI-generated formula so that a copied mistake does not validate itself.

For a completion percentage, for example, consider ordinary counts, zero eligible cases, missing eligible cases, more completed than eligible and a valid zero completion count. The correct treatment of each case comes from the defined rule. Do not hide every error with a blank or zero simply to make the dashboard attractive.

Assume an invented US operations team defines completion rate as completed eligible tasks divided by all eligible tasks. It decides that no eligible tasks should display not applicable, missing population data should display missing, and completed tasks exceeding the population should trigger review. These are invented local rules, not a universal reporting standard.

The test set contains 8 of 10, 0 of 10, 0 of 0, 8 with a missing denominator and 12 of 10. Expected outputs are 80 percent, zero percent, not applicable, missing and review. A suggested formula that turns every division error into zero fails at least two of those cases even though it produces a neat column.

The reviewer corrects the logic and reruns the same cases, then checks copied formulas in the intended range. Passing five cases supports this limited acceptance decision; it does not establish that the entire workbook is error-free or statistically validated.

Check whether relative and absolute references point to the intended inputs after copying or inserting rows. Look for numbers stored as text, hidden filters, external links and inconsistent date formats. Confirm that the formula has not replaced an input field or overwritten a manual exception without authorisation.

Compare totals or independent control figures where available. A row-level formula can be correct while a total excludes part of the dataset. Ask a second competent person to review high-impact logic, particularly when the author used the same AI system to generate both formula and explanation.

Record the plain-language rule, formula version, test cases, results, limitations and reviewer. Preserve the previous working version and a controlled rollback route. Apply protection or access controls that help prevent accidental edits without obstructing authorised maintenance.

Use AI workflows with meaningful human checks to define the human decision around the tool. Recheck affected calculations when columns, source data, software or business rules change. Do not describe the workbook as audited or independently assured unless that work has actually been performed by the appropriate party.

No. It can help identify issues, but it is not independent evidence. Use known answers and appropriate human review.

No. Zero, missing information and not applicable describe different conditions. Preserve the distinction required by the business rule.

It can contribute to a review, but consequential financial models require an appropriately scoped specialist validation. A small formula test is not a substitute.

ICAEW twenty principles for good spreadsheet practice — 2024 fourth edition; professional guidance, not a US statutory control standard.

NIST Generative Artificial Intelligence Profile — Final NIST AI 600-1, July 2024; voluntary risk guidance. Original workflows in these articles are not NIST prescriptions.

QSE Advisory is part of the QSE Advisory brand family. Preparation included AI-assisted research and drafting. Examples are hypothetical.

This article is general information. Verify current requirements and seek qualified advice for your specific circumstances.

Spotted an issue? Contact our editorial desk.