Article
BeginnerWhy an AI-Written Spreadsheet Formula Can Look Right and Still Be Wrong
A chatbot's formula can return sensible numbers on every row you glance at and still apply the wrong rule. Research on spreadsheets from 2000 and a 2023 university study of AI-written Python code point to the same habit: test on rows where you already know the answer.
AI Literacy Data Quality Finance Events
Picture a meetings coordinator building the registration sheet for next spring’s annual meeting. The fee rule has a few moving parts: members and nonmembers pay different rates, there is an early-bird discount up to a cutoff date, and students pay a flat fee. She types the rule into a chatbot, asks for a formula, and pastes the answer into the fee column. She checks three rows. A member who registered in February shows the discounted member rate. A nonmember who registered in April shows the full nonmember rate. Another member shows the right number too. The sheet goes to the finance team, and invoices go out from it.
This is a teaching example, not a case study. But anyone who has asked an AI tool to “write me a formula” will recognize the moment. The formula looks expert, the numbers look sensible, and nothing on the screen suggests a problem. Yet the formula could still charge a nonmember who registered on the cutoff day the full price, give a student the nonmember rate, and hand the discount to anyone whose registration date is missing. None of those rows were among the three she checked.
Spreadsheets do not forgive small mistakes
Long before chatbots, researchers were studying how people get spreadsheets wrong. Raymond Panko of the University of Hawaii summarized that work in a paper for a European spreadsheet risk conference in 2000. His subject was spreadsheets built by people, not by AI, so his paper says nothing about how often an AI tool gets a formula wrong. What it does describe well is how spreadsheets fail, and that part has not aged.
His starting point: “Unfortunately, errors in bottom-line values are very likely because spreadsheet modeling is incredibly unforgiving of errors.” He contrasts a spelling mistake in a document, which rarely matters, with a spreadsheet, “but an error almost anywhere in a spreadsheet will produce an incorrect bottom-line value.” One wrong fee cell flows into a registration total, a revenue projection, and a board report.
Panko also sorts errors into types, and two of them matter here. “Logic errors occur when the developer has the wrong algorithm for a particular formula cell or expresses the algorithm incorrectly in the formula.” Using “before the cutoff” where the rule says “on or before the cutoff” is that kind of error. “Omission errors occur when the developer omits something that should be in the model.” A formula with no branch for students is that kind. Neither looks like a mistake when you read the formula, and neither shows up on an ordinary row.
The third type, mechanical errors such as a mistyped cell reference, is the one most people picture when they think of spreadsheet mistakes. An AI-written formula usually avoids the typos. The question is whether it encodes the right rule, and that is not something you can tell by looking at it.
Why a quick glance feels like enough
Panko found that people are poor judges of their own sheets: “spreadsheet developers are overconfident in the accuracy of their spreadsheets.” He also found that checking is the exception rather than the rule: “In spreadsheet development, testing is fairly rare”.
A chatbot’s formula can add a second layer of confidence. When you write a formula yourself, you know which parts you were unsure of. When a tool hands you a long nested formula in seconds, it is easy to assume the tool understood the rule better than you would have. The formula is also harder to read than one you wrote, so a careful read is less likely to happen.
Meanwhile the rows you look at first are usually typical ones. Most registrations are members or nonmembers who signed up well before or well after the deadline. Those are exactly the rows a flawed formula is most likely to get right.
What research on AI-written code found
No source we found measures how often AI-written spreadsheet formulas are wrong, and we will not guess at a rate. The closest evidence comes from research on AI-written code, and a formula is a small piece of code.
In 2023, Jiawei Liu and colleagues at the University of Illinois Urbana-Champaign and Nanjing University tested AI-written Python functions, not spreadsheet formulas, from 2023 models. They started with a widely used benchmark that checks each generated function against a set of tests. Their concern was the tests themselves: “However, these test-cases can be limited in both quantity and quality for fully assessing the functional correctness of the generated code.”
So they added many more tests to the same problems and reran the evaluation. Their summary: “Our extensive evaluation across 26 popular LLMs (e.g., GPT-4 and ChatGPT) demonstrates that HumanEval+ is able to catch significant amounts of previously undetected wrong code synthesized by LLMs, reducing the pass@k by up-to 19.3-28.9%.” Put plainly, in a 2023 university study of Python coding problems, not spreadsheets, code that had passed the original tests was often caught as wrong once the tests got more thorough. That figure describes those models on those problems; it is not an error rate for the formula in your sheet, and current chatbots may do better or worse.
They also found that thin tests could mislead people choosing between tools: “We also surprisingly found that test insufficiency can lead to mis-ranking.” A model that looked better on the short test was not always better on the long one.
The lesson carries over even though the setting differs. Code that passed a few checks was not shown to be correct. It was shown to handle the cases someone happened to check. The coordinator’s three rows are the same kind of short test.
The habit that catches it
Panko’s paper describes the remedy in passing: “One is execution testing, in which known data are used to test the spreadsheet.” For an AI-written formula, that means building a few rows where you already know the right answer, working out that answer by hand from the written rule, and seeing whether the formula agrees.
The useful rows are the uncomfortable ones. For the registration sheet, that means a row dated exactly on the cutoff, a row for every member type the rule mentions, and a row with a blank cell, alongside one ordinary row. In many spreadsheet apps a blank date compares as earlier than any real date, so a formula that checks “before the cutoff” can quietly give the discount to anyone with no date at all. Only a test row with a blank date will show you that.
A few practical points help this stick:
- Write the rule down first. If you cannot say in two sentences what the formula should do, you cannot test whether it does it.
- Work out the answer before you run the formula. Seeing the formula’s number first makes it look right.
- Treat any mismatch as a stop. Find out why before the sheet is used. Do not adjust your hand answer to match.
- Have someone else look. A colleague who did not write the prompt reads the rule fresh.
Our tutorial on testing an AI-written spreadsheet formula on rows where you already know the answer walks through those steps with a working lab, using the registration example above. For related checks, see our guides to member export damage, dates in a dues export and numbers in an AI summary.
None of this means you should stop asking AI tools for formulas. They are often a fast way to get a working draft, especially for nested conditions that are tedious to write by hand. The point is to treat the formula as a draft until it has passed rows you chose because they are hard. A formula that has only seen easy rows has not been tested yet.
Sources (2)
- Raymond R. Panko (University of Hawaii): Spreadsheet Errors: What We Know. What We Think We Can Do. (Proceedings of the EuSpRIG Spreadsheet Risk Symposium, Greenwich, England, July 2000; posted as arXiv:0802.3457)
- Liu, Xia, Wang and Zhang (University of Illinois Urbana-Champaign; Nanjing University): Is Your Code Generated by ChatGPT Really Correct? Rigorous Evaluation of Large Language Models for Code Generation (arXiv:2305.01210v3, October 30, 2023)