# Get a spreadsheet formula from AI, then prove it works

Describe the sheet exactly, ask for the formula with its edge cases, run it on 8 to 12 rows you worked out by hand, check for silent failures, and only then fill down and reconcile one known total.

Workflows · v1.0 · draft · https://hayzlabs.com/workflows/spreadsheet-formula-then-test/

In short:
- Describe the sheet and app version exactly, and ask for the formula with its edge cases and without IFERROR.
- Test it on a block of rows you worked out by hand, including a blank, a duplicate and a row that should not match.
- Check for silent failures such as approximate-match lookups and shifting references, then fill down and match one known total.

**Use it when** you ask a chatbot for an Excel or Google Sheets formula, it looks right, and you are about to fill it down across thousands of rows.

**Not when** you want the assistant to answer a question about the data itself. Use [Asking questions of your data](https://hayzlabs.com/workflows/asking-questions-of-your-data/). If you are matching two files that should agree, use [Reconcile two exports](https://hayzlabs.com/workflows/reconcile-two-exports/).

**You need** the sheet, a spare tab, and about 30 minutes the first time. Paste header names and made-up example values, not real customer rows: [What not to paste](https://hayzlabs.com/start/what-not-to-paste/). The steps and times are our suggestion.

## Why test a formula that looks right

Spreadsheets were error-prone long before AI. In Panko's review, the more careful field audits from 1997 to 2000 "found errors in at least 86% of the spreadsheets audited", and developers "are overconfident in the accuracy of their spreadsheets" ([Panko, 2000](https://arxiv.org/pdf/0802.3457), review of field audits). Those audits were small and mostly counted serious errors only. Models are not past this either: a benchmark (a fixed set of test questions) of 912 real questions from Excel forums found "a substantial gap" between the best models and people ([SpreadsheetBench, 2024](https://arxiv.org/abs/2406.14991), benchmark, preprint). This page applies the general routine in [Check before you trust](https://hayzlabs.com/start/check-before-you-trust/) to formulas.

## Steps

| # | Step | Who | Time | Check |
|---|---|---|---|---|
| 1 | Describe the sheet: tab name, each column's header and two example values, where the result goes, and the app and version (Excel 365, Excel 2019, Google Sheets). Say whether your formulas use commas or semicolons between arguments. | Person | 5 min | A stranger could rebuild the layout from your note. |
| 2 | Ask for the formula, a plain-words explanation, and the edge cases it handles and does not handle. Use the prompt below. | Person and AI | 3 min | You can say in one sentence what the formula does. **If it fails:** ask it to explain each part again. |
| 3 | On a spare tab, build a test block of 8 to 12 rows and write the expected answer for each by hand. Include a blank, text in a number column, a duplicate, a zero, a month-end date and a value that should not match. | Person | 10 min | Every row has an expected answer you worked out without the formula. |
| 4 | Put the formula on the test block and compare each result with your answer. | Person | 3 min | Every row matches. **If it fails:** paste the failing rows back to the model with the expected answers, and test again. |
| 5 | Check for silent failures (list below). | Person | 5 min | You can say why each one does or does not apply. |
| 6 | Fill down on the real data. Compare one total, count or row you already know from another source. | Person | 3 min | The known figure matches. **If it fails:** stop and find the rows that differ before you use any result. |
| 7 | Hide the test tab. Note the formula, the date and the model on it. | Person | 1 min | The next person can re-run the test. |

> [!OUR RULE]
> No formula goes on real data until it passes a test block we worked out by hand, including the row that should not match.

## Silent failures to check

These return a plausible answer instead of an error.

- **IFERROR hides everything.** It returns your fallback for #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? and #NULL! alike ([Microsoft](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)). A typo in a range then looks like "not found". Run the test block without IFERROR first, and add it last if you need it.
- **VLOOKUP defaults to approximate match.** In Excel, if range_lookup is left out, "the default value will always be TRUE or approximate match", which assumes the first column is sorted ([Microsoft](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)). Google Sheets is the same: "If `is_sorted` is not specified, it is `TRUE` by default" ([Google](https://support.google.com/docs/answer/3093318)). On unsorted data you can get a wrong value with no error. Ask for `FALSE` to be written in.
- **XLOOKUP defaults to exact match** and returns #N/A when nothing is found, unless you give it an if_not_found value ([Microsoft](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)). It is "not available in Excel 2016 and Excel 2019", which is why step 1 names the version.
- **References that move when filled down.** A relative reference shifts as you copy it; a `$` fixes the column, the row or both, and F4 cycles through them in Excel ([Microsoft](https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9)). Check the formula in the last row of the test block, not only the first. A lookup table without `$` slides down as you fill.
- **Whole-column references.** `A:A` takes in the header and any totals or notes below the data. Our suggestion: check what the range would pick up, or use a bounded range or a table.
- **Separators.** Excel expects the list separator set in Windows regional settings, and comma is the default for US English; a formula typed with another separator gives a "problem with this formula" error ([Microsoft](https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/formula-errors)). Tell the model which one you use.

Try the fill-down failure on a small VAT column (illustrative). Change how C2 refers to the rate and read the formula in the last row.

<!-- WIDGET fillDown -->

> [!CHECK]
> After step 5, read the formula in the last row of the test block. If any reference points somewhere you did not expect, fix the `$` signs before step 6.

## The prompt

```text title="Formula prompt · v1.0"
I need a formula for <APP_AND_VERSION>. My formulas use <SEPARATOR> between arguments.
Tab: <TAB_NAME>. Data runs from row <FIRST_ROW> to about row <LAST_ROW>.
Columns (header: example values):
<COLUMN_LIST>
Put the result in column <RESULT_COLUMN>. It should: <WHAT_IT_SHOULD_DO>
Give me:
1. The formula for the first data row, ready to fill down.
2. A plain-words explanation of each part.
3. What it returns for: a blank cell, text in a number column, a duplicate key, a zero, a month-end date, and a value with no match.
4. Any default it relies on (match type, sorting), written out explicitly.
Do not wrap it in IFERROR.
```

Replace the following:
- `<APP_AND_VERSION>`: such as `Excel for Microsoft 365 on Windows` or `Google Sheets`.
- `<SEPARATOR>`: `commas` or `semicolons`.
- `<TAB_NAME>`: the tab's exact name.
- `<FIRST_ROW>` and `<LAST_ROW>`: the first data row and roughly the last.
- `<COLUMN_LIST>`: one line per column, such as `B Invoice date: 31/01/2026, 15/02/2026`. Use made-up values.
- `<RESULT_COLUMN>`: the column letter for the answer.
- `<WHAT_IT_SHOULD_DO>`: one sentence, such as `return the unit price from tab Prices for the SKU in column C, or "missing" if there is none`.

## The test block

```text title="Test block · v1.0"
case,<INPUT_COLUMNS>,expected,formula_result,match
blank input,,<EXPECTED>,,
text in number column,<VALUE>,<EXPECTED>,,
duplicate key (first),<VALUE>,<EXPECTED>,,
duplicate key (second),<VALUE>,<EXPECTED>,,
zero,0,<EXPECTED>,,
month-end date,<DATE>,<EXPECTED>,,
should not match,<VALUE>,<EXPECTED>,,
normal case 1,<VALUE>,<EXPECTED>,,
normal case 2,<VALUE>,<EXPECTED>,,
last row of range,<VALUE>,<EXPECTED>,,
```

Replace the following:
- `<INPUT_COLUMNS>`: the column headers the formula reads, copied exactly.
- `<VALUE>` and `<DATE>`: inputs you choose for each case.
- `<EXPECTED>`: the answer you worked out by hand, before running the formula.

Fill `formula_result` from the formula, and set `match` with a simple equality check, such as `=D2=E2`. Add rows for anything specific to your data.

## Built-in assistants

Copilot in Excel opens from the Copilot icon in the lower right of Excel and can explain a formula; Microsoft says to "review, edit, and verify anything Copilot creates" ([Microsoft](https://support.microsoft.com/en-us/excel/copilot/generate-single-cell-formulas-with-copilot-in-excel)). In Google Sheets, Gemini can create a formula from the side panel on eligible plans, and Google warns it may suggest inaccurate information ([Google](https://support.google.com/docs/answer/14218565)). The same test block applies.

## How to tell it worked

- Every row of the test block matches, including the blank and the row that should not match.
- One total or count you knew in advance matches after the fill-down.
- The hidden tab has the formula, the date and the model, and the test re-runs when the formula changes.
- It has failed if you edited the expected answers after seeing the formula's results.

> [!LIMIT]
> A test block catches only the cases you thought to put in it. A pass on 12 rows does not prove the formula right on every row, and a matching total can hide errors that cancel out. For figures that feed tax, payroll or accounts, have a second person check the formula as well.

## Sources

1. Panko, 2000. "Spreadsheet Errors: What We Know. What We Think We Can Do." Review of field audits and experiments. [Link](https://arxiv.org/pdf/0802.3457)
2. SpreadsheetBench, 2024. 912 real spreadsheet questions from Excel forums. Preprint benchmark. [Link](https://arxiv.org/abs/2406.14991)
3. Microsoft. VLOOKUP, XLOOKUP and IFERROR function pages; relative and absolute references; list separator errors. Vendor documentation. [VLOOKUP](https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1), [XLOOKUP](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), [IFERROR](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611), [References](https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9), [Separators](https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/formula-errors)
4. Google. VLOOKUP in Sheets; Gemini in Sheets. Vendor documentation. [VLOOKUP](https://support.google.com/docs/answer/3093318), [Gemini](https://support.google.com/docs/answer/14218565)
5. Microsoft. Copilot in Excel. Vendor documentation. [Link](https://support.microsoft.com/en-us/excel/copilot/generate-single-cell-formulas-with-copilot-in-excel)