Articles

How do you test an Excel formula written by AI?

Four invented rows, an answer computed by hand, and a status cell. A formula that is wrong on four rows is wrong on twenty thousand.

Test a formula on a mini sample: a few invented rows, small enough to compute by hand, that contain every case the formula must handle. Write the expected answer first, apply the formula, and compare. In our case, a weighted average lease term written as a plain average returns 3.50 years on the sample against 4.00 expected. On real data, the wrong result looks just as plausible as the right one.

The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →

Why a plausible result proves nothing

A wrong formula rarely returns an error. It returns a number of the right size. Comparing with last quarter's report does not help: the data changed, and the old report may contain the same mistake. Only an exact expected answer separates right from nearly right.

The mini sample

Four invented leases.
LeasePropertyTenantArea (sqm)Rent (EUR k)Term (years)
1XT11001002.0
2XT22002005.0
3XVacant10000.0
4YT3400801.0

Round numbers, the same columns as the real data, a vacant row, and a row from another property: a formula that forgets the property criterion will give a wrong answer.

Expected answers, by hand

Property X: what a correct formula must return, and what common mistakes return.
MeasureExpectedBy handA common mistake returns
Passing rent300100 + 200380 (no property criterion)
WALT, rent-weighted4.00(100 x 2 + 200 x 5) / 3003.50 (plain average of 2 and 5)
Vacancy rate, by area25%100 / 40033% (divided by the let area)
Rent per let sqm1,000300 x 1,000 / 3001 (the factor 1,000 forgotten)

Keep the test in the workbook

Put the sample on its own sheet, with three columns: the result of the formula, the expected answer typed by hand, and a status, =IF(ABS(result-expected)<=tolerance,1,0). A last cell counts the tests passed. The test then runs every time the file recalculates, including after someone edits a formula.

Three rules

What the test does not cover

A test proves a formula on its sample. It does not prove that the ranges cover the real table, or that the totals of the real model agree. That is the job of checks: the same total by two independent routes.

Sources

  1. Julian R. Sterling, AI in Excel for Analysts (video course, 2026): running case Brackenridge portfolio
  2. Microsoft Support, function reference for SUMIFS, SUMPRODUCT, INDEX, MATCH and IFERROR
  3. ICAEW, Twenty principles for good spreadsheet practice; R. Panko, What We Know About Spreadsheet Errors

Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.

Formulas you can prove, step by step

26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).

Watch module 1 free →

Questions readers ask

How can I check that a formula from an AI assistant is correct?

Apply it to a few invented rows whose answer you computed by hand, including the unusual cases, and compare. A plausible result on the real data is not a check.

How many rows does a test sample need?

As few as cover every case: often four to six. Ordinary rows, a zero or blank, a repeated key, and a row that must be excluded.

Who should compute the expected answer?

You, by hand. If the assistant writes both the formula and the expected answer, the same misunderstanding can pass the test.

Read the whole case

This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.

See the course →Free companion files

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada

Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99

Also on this site

All 2164 articles →