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 →
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.
| Lease | Property | Tenant | Area (sqm) | Rent (EUR k) | Term (years) |
|---|---|---|---|---|---|
| 1 | X | T1 | 100 | 100 | 2.0 |
| 2 | X | T2 | 200 | 200 | 5.0 |
| 3 | X | Vacant | 100 | 0 | 0.0 |
| 4 | Y | T3 | 400 | 80 | 1.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.
| Measure | Expected | By hand | A common mistake returns |
|---|---|---|---|
| Passing rent | 300 | 100 + 200 | 380 (no property criterion) |
| WALT, rent-weighted | 4.00 | (100 x 2 + 200 x 5) / 300 | 3.50 (plain average of 2 and 5) |
| Vacancy rate, by area | 25% | 100 / 400 | 33% (divided by the let area) |
| Rent per let sqm | 1,000 | 300 x 1,000 / 300 | 1 (the factor 1,000 forgotten) |
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.
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.
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.
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).
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.
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.
You, by hand. If the assistant writes both the formula and the expected answer, the same misunderstanding can pass the test.
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