Do not trust an AI-suggested Excel formula after testing only normal rows. Use a small synthetic sheet to test blanks, zero, text, and dates, then cross-check the intended results with a simple Python script.
Who this is forPeople who use an AI chat assistant to create Excel formulas and want to test those formulas before applying them to real workbooks.
An AI chat assistant can suggest an Excel formula that looks correct on ordinary data but behaves differently when a cell is blank, the denominator is zero, text appears where a number was expected, or a date is stored as an Excel serial number. These cases matter because spreadsheet errors are not always visible as #DIV/0! or #VALUE!. A formula can also return a plausible-looking number that is logically wrong.
The example below is synthetic. Suppose column A contains an old value, column B contains a new value, and column C should calculate percentage change as (new - old) / old. The business rule is that blanks stay blank, zero denominators and non-numeric values are marked CHECK, and dates must not be treated as ordinary measurements.
Create a small test sheet before applying the formula to a real workbook. Use the following rows so normal values and common failure cases are visible together.
| Row | A: Old value | B: New value | Intended result |
|---|---|---|---|
| 2 | 100 | 120 | 20% |
| 3 | 50 | 50 | 0% |
| 4 | 25 | blank | |
| 5 | 0 | 10 | CHECK |
| 6 | pending | 10 | CHECK |
| 7 | 100 | n/a | CHECK |
| 8 | 2026-10-01 | 2026-10-08 | CHECK |
The first two rows establish the normal behavior: (120 - 100) / 100 = 0.20, or 20%, and (50 - 50) / 50 = 0%. The remaining rows are tests rather than ordinary production data.
A simple request to an AI chat assistant might be: Calculate percentage change from A2 to B2. Leave the result blank if there is an error.
This formula works for 100 to 120 and 50 to 50. However, IFERROR hides several different problems behind the same blank result. A zero denominator, text input, or another formula error may disappear instead of being identified for review. More importantly, Excel stores dates internally as serial numbers. If A2 and B2 contain real Excel dates, subtraction and division can produce a numeric percentage instead of an obvious error.
That means a successful calculation is not sufficient evidence that the inputs belong to the intended data type.
For the stated rules, a more explicit formula is:
This version distinguishes blanks, non-numeric inputs, and a zero denominator. Rows 2 through 7 therefore behave as intended. Format numeric results in column C as Percentage if you want 0.20 displayed as 20%.
Row 8 exposes an important limit. Excel dates are numbers underneath their displayed date format, so ISNUMBER returns TRUE for a real Excel date. The defensive formula can therefore still calculate with dates. A generic formula cannot reliably infer whether a numeric serial represents an intended measurement or an unintended date merely from its numeric value.
For a second check, save the synthetic cases as formula_cases.csv with columns row,old_value,new_value. Keep 2026-10-01 and 2026-10-08 as shown. The following standard-library script applies the intended rule independently: blanks remain blank, ISO-formatted dates are marked CHECK, non-numeric values are marked CHECK, zero old values are marked CHECK, and valid numbers are used for percentage change.
from pathlib import Path
from decimal import Decimal, InvalidOperation
from datetime import date
import csv
import sys
INPUT_PATH = Path("formula_cases.csv")
OUTPUT_DIR = Path("outputs")
REPORT_PATH = OUTPUT_DIR / "formula_check_report.txt"
if not INPUT_PATH.is_file():
sys.exit("Missing input file: formula_cases.csv")
if OUTPUT_DIR.exists():
sys.exit("Stop: outputs folder already exists. Remove or rename it manually first.")
def is_iso_date(value):
try:
date.fromisoformat(value)
return True
except ValueError:
return False
def check_change(old_text, new_text):
old_text = old_text.strip()
new_text = new_text.strip()
if old_text == "" or new_text == "":
return "blank"
if is_iso_date(old_text) or is_iso_date(new_text):
return "CHECK"
try:
old = Decimal(old_text)
new = Decimal(new_text)
except InvalidOperation:
return "CHECK"
if old == 0:
return "CHECK"
change = (new - old) / old * Decimal("100")
return f"{change:.2f}%"
results = []
with INPUT_PATH.open("r", encoding="utf-8", newline="") as file:
reader = csv.DictReader(file)
required = {"row", "old_value", "new_value"}
if reader.fieldnames is None or not required.issubset(reader.fieldnames):
sys.exit("CSV must contain: row, old_value, new_value")
for record in reader:
result = check_change(record["old_value"], record["new_value"])
results.append(
f"Row {record['row']}: "
f"old={record['old_value']!r}, "
f"new={record['new_value']!r} -> {result}"
)
OUTPUT_DIR.mkdir()
REPORT_PATH.write_text("\n".join(results) + "\n", encoding="utf-8")
print(f"Created: {REPORT_PATH}")
For the seven synthetic rows, the expected Python results are 20.00%, 0.00%, blank, CHECK, CHECK, CHECK, and CHECK. Compare these results with the spreadsheet row by row rather than checking only whether Excel displays an error.
Python is useful here because it provides an independent implementation of the rule. It should not simply reproduce the Excel formula character for character. If both implementations make the same assumption, the cross-check may repeat the same mistake.
The main lesson is that formula verification is a data-type and business-rule problem, not only a syntax problem. An AI-generated formula is a draft implementation. Edge-case tests tell you whether that implementation matches the behavior you actually need.
ChatGPT Python tool · Python 3.x · standard library pathlib, decimal, datetime, csv, sys
The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.