Test an AI-suggested Excel formula on blanks, zero, text, and dates

01Why a formula that works on one row can still be wrong

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.

02Build a small sheet with deliberate edge cases

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.

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.

03Ask the AI for a formula, then inspect its assumptions

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.

04Make the formula more explicit and test every row

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.

05Cross-check the intended rule with Python

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.

06Common mistakes when checking AI-suggested formulas

• Testing only one normal row and then filling the formula down thousands of rows.
• Using IFERROR to hide every failure without distinguishing missing data from invalid data.
• Forgetting that dividing by zero requires an explicit business rule.
• Assuming numeric-looking or formatted cells always contain the intended type of data.
• Forgetting that Excel dates are stored as numbers and can participate in arithmetic.
• Comparing displayed percentages without checking whether the underlying value is 0.20, 20, or another scale.
• Changing the formula until the visible errors disappear instead of defining expected outputs first.

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.

07Use a final formula test checklist

1. Write the intended calculation in plain language before accepting a formula.
2. Create normal, blank, zero, text, and date test rows.
3. Calculate the simple expected values by hand.
4. Check whether errors should be blank, flagged, or stopped rather than hidden automatically.
5. Confirm how dates and other special Excel types are represented.
6. Cross-check the rule with an independent calculation when the result matters.
7. Only then apply the formula to the full dataset.

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.

Execution and verification record

2026-09-21 · hand-checked example · Python 3.12

• Checked by hand that (120 - 100) / 100 = 0.20, or 20%.
• Checked by hand that (50 - 50) / 50 = 0, or 0%.
• Checked that a blank input should produce blank under the stated synthetic rule.
• Checked that an old value of 0 cannot be used as the denominator and is therefore marked CHECK.
• Checked that pending and n/a are non-numeric inputs and are marked CHECK.
• Checked that the Python rule explicitly detects the ISO dates 2026-10-01 and 2026-10-08 and marks the row CHECK.
• Checked that the script reads formula_cases.csv, writes only outputs/formula_check_report.txt, and stops if the outputs folder already exists.

Site-wide writing and verification principles

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Python csv documentation ↗ (New tab)
• Python decimal documentation ↗ (New tab)
• Python datetime documentation ↗ (New tab)