prepared by the NADALI editorial team using AI
A small-business owner asks AI for a spreadsheet formula to calculate a monthly total. The model supplies a formula, and the result looks plausible. Before using that figure in a management report, check three things: whether the measure is defined correctly, whether the inputs are suitable and whether the formula implements the agreed rule.
You do not need to investigate the entire workbook at once. Start with a separate copy and a dataset small enough to check by hand. The editorial workflow below uses entirely fictional data. Expected totals were calculated separately from the formula; the editorial team did not run this example in the Excel or Google Sheets interfaces. This is a spreadsheet-logic check, not accounting, tax or financial advice.
1. Define the measure in words first
Replace “calculate the September result” with a short rule: “Add Amount for branch North with status Done, from 1 September 2026 inclusive to 1 October 2026 exclusive. Each ID represents one record. All values use the same illustrative unit.”
Specify which date matters: creation, completion or another event. Decide how corrections, cancellations and missing values are represented. Do not let the model choose these rules. A correctly written formula for the wrong definition will still answer the wrong question.
For the working file, retain the original export and the date it was obtained. Record the column names and row count. The structure and synthetic rows are enough for an initial AI request. Do not send customer contacts, personal information or confidential business data to an unapproved service.
2. Build a small reference dataset
Row 1 should contain ID, Date, Branch, Status and Amount in columns A–E. The Row label below only identifies the spreadsheet row number: do not paste it as an additional data column. North and Done remain identical in both language versions of this article so that the formula criteria do not change.
| Row | A: ID | B: Date | C: Branch | D: Status | E: Amount |
|---|---|---|---|---|---|
| 2 | R01 | 2026-09-01 | North | Done | 120.40 |
| 3 | R02 | 2026-09-03 | North | Done | 79.60 |
| 4 | R03 | 2026-09-05 | North | Cancelled | 50.00 |
| 5 | R04 | 2026-09-08 | South | Done | 90.00 |
| 6 | R05 | 2026-08-31 | North | Done | 25.00 |
| 7 | R06 | 2026-09-30 | North | Done | 100.00 |
| 8 | R07 | 2026-10-01 | North | Done | 30.00 |
| 9 | R08 | 2026-09-12 | North | Done | 0.00 |
These amounts are invented, not market prices. Enter Amount as numbers and Date as actual spreadsheet dates, not text labels. You can create the first date with =DATE(2026,9,1), then adjust the month and day for the other rows. The YYYY-MM-DD display in the example is intended to make the dates unambiguous to readers.
DATE builds a date from a year, month and day. It does not, however, validate whether the entered components describe a valid calendar date: the Excel and Google Sheets documentation describes rolling excess days or months into another period. Check the input components too.
Establish the answer by hand before examining the formula. R01, R02, R06 and R08 qualify: four records. The reference total is 120.40 + 79.60 + 100.00 + 0.00 = 300.00. R03 is excluded by status, R04 by branch, and R05 and R07 by date. This list can reveal an error even when the grand total happens to match.
3. Ask for an explanation of each formula component
An example AI request is: “Suggest a formula for the stated rule and columns A–E. Explain every range and criterion. Do not change the data or assume rules for missing values, duplicates or rounding. List the cases that require separate checks.”
For this dataset, a candidate to test is:
=SUMIFS(E2:E9,C2:C9,"North",D2:D9,"Done",B2:B9,">="&DATE(2026,9,1),B2:B9,"<"&DATE(2026,10,1))This version uses English function names and commas between arguments. Your environment may require localised names or a different separator. Check the syntax against your spreadsheet’s own formula assistance rather than changing workbook-wide settings merely to paste this example.
SUMIFS adds values subject to multiple criteria. Microsoft’s documentation requires criterion ranges to match the dimensions of the sum range. Google documents the same basic argument order. Here, E2:E9 contains amounts, C2:C9 checks North, D2:D9 checks Done, and the two conditions applied to B2:B9 set the start and end of the period.
Match those references to the columns yourself. The formula does not check ID uniqueness, export completeness or the business meaning of Done. An explanation from another model is not an independent calculation either: a person checks the selected rows and arithmetic.
4. Test the boundaries most likely to cause mistakes
Change only one condition at a time in a copy of the reference dataset. Restore the original data after each test. Keep the expected outcome, the actual result from your application and a decision of “accepted” or “needs correction”.
Blank versus zero. Replace R08’s 0.00 with a genuinely empty cell. Even if the total remains 300.00, the dataset is now incomplete: zero was a confirmed value, whereas blank means information is missing. For this entire teaching range, =COUNTBLANK(E2:E9) should show 0 before the change and 1 afterwards. Excel does not count zero values as blank; Google Sheets, like Excel, counts empty text returned by a formula. This check does not detect every problem, such as numbers stored as text. In a working dataset, specify which fields and records may legitimately be missing.
Duplicate. Add a copy of R02 in row 10 and deliberately extend every formula range to row 10. The expected mechanical sum becomes 379.60, but it should not be accepted: the same amount has been included twice. Check whether ID really is unique under the source’s rules. If one ID legitimately has several lines, use an appropriate key, such as ID plus line number. Do not automatically remove records just because their names or amounts match.
Month boundary. The baseline includes 1 September and excludes 31 August and 1 October. As another test, replace R06’s date with an actual date-and-time value for 30 September at 18:00. It should still qualify, leaving the total at 300.00. The “less than 1 October” condition deliberately includes the whole final day. Where source data spans time zones, agree on the reporting zone and time conversion first; this formula does not reconcile time zones itself.
Locale and data types. Do not rely on ambiguous text such as 09/10/2026. Verify how the application read the day and month, as well as numbers using decimal commas or points. In Google Sheets settings, locale affects default date, number and currency formatting for the whole spreadsheet; function language is a separate setting. If a value has not been recognised correctly, fix the import in a copy using an agreed format rather than adjusting the formula to produce a plausible answer.
5. Define the rounding rule separately
All baseline values already have no more than two decimal places. For a separate test, take two values of 10.004. Rounding each to two decimal places and then adding gives 20.00. Adding first gives 10.004 + 10.004 = 20.008; rounding that total gives 20.01.
The candidate formulas to test are =ROUND(10.004,2)+ROUND(10.004,2) and =ROUND(10.004+10.004,2). Microsoft and Google document ROUND as rounding to a specified number of digits or decimal places. Choosing when to round depends on the definition of your measure, not on the model. Merely displaying two decimal places does not specify that rule. Record the agreed method and check it again after changes.
6. Apply the checks to the working file
After the teaching tests, manually reconcile a small sample from the real file in an approved environment. Include boundary dates, zero values and excluded records. Compare the imported row count with the source. Append a new qualifying record to the end of a copy: fixed ranges such as E2:E9 and B2:B9 do not include row 10 unless the references change. The rule for expanding the dataset must be explicit.
Do not hide an unexplained error behind a zero or replace an unverified value with the “most likely” one. Leave the total unapproved, return to the previous verified version and investigate the cause. A change to status labels, export structure, reporting period or rounding policy should trigger another test.
Before using the total, check that:
- the measure, unit, event date and period boundaries are written down;
- qualifying records are identified, and their count and total match an independent calculation;
- missing values, data types, repeated keys and rounding have separate checks;
- the formula has been tested in your application and locale, including a newly appended final row;
- the source version, formula, test results and responsible person are recorded.
Related reading
Read more about data boundaries for AI, a short working brief and human review of AI outputs.
Sources and limitations
The primary Microsoft and Google documentation was read on 20 October 2026. It supports the function and settings descriptions. The dataset, AI request, test cases and approval workflow are editorial proposals. Agreement across eight rows does not establish that an entire management report is correct; it provides a reference for testing a specific rule in your own file.
Cover: an original AI-generated illustration for Nadali, not an interface screenshot or evidence of testing.
