Check an AI-written Excel formula before using it
Test an AI-generated Excel formula with small known answers, blank cells and copied rows. Catch a wrong calculation even when Excel shows no error.
In brief
Treat an AI-written formula as a draft. Test it on a separate sheet with answers you can calculate yourself before applying it to grades, invoices or a large workbook. A formula that returns a number can still implement the wrong rule.
Excel can detect many syntax and reference errors, but it cannot know whether you intended a discount before tax or after it. You need a small example that makes that decision visible.
Describe the rule before asking for a formula
Suppose column A holds a price and column B holds a discount entered as a percentage. The required result is the price after that discount. State whether a blank discount means zero, whether negative prices are allowed and when rounding should happen. An assistant cannot infer your accounting rules from two column names.
Use invented rows when requesting help: 80 with 25% should give 60; 80 with 0% should give 80; 0 with 25% should give 0. These are arithmetic examples, not sample customer records.
Illustrative example
A2 = 80; B2 = 25%; expected result = 60. A draft formula is =A2*(1-B2). Enter 25% in B2, not the number 25.
Test the failures as well as the normal row
Add a blank cell and a text value such as “pending”. Decide whether these should produce a blank, an explicit error or a result. Do not immediately wrap the formula in IFERROR: hiding an error may make an incomplete row look like a valid zero.
Then copy the formula to another row. A2 normally becomes A3; $A$2 stays fixed. If a shared tax rate is stored in one cell, inspect whether that reference must remain anchored. Compare the copied formula with the rule rather than assuming a successful copy is a correct one.
Inspect how Excel reaches the result
In Excel for Windows, select the formula cell and look under Formulas > Formula Auditing > Evaluate Formula. Step through the calculation where the feature is available. This can reveal which input is used at each stage; it is not a certificate of correctness.
Documentation for these steps: Microsoft — SUMIFS ↗
Keep a short acceptance check
Write down the expected answers beside your test rows and retain them with the workbook. Re-run them after changing the formula or rearranging columns. For a consequential calculation, ask someone responsible for that work to check the rule as well as the arithmetic. The useful deliverable is a formula plus examples that justify it, not a confident explanation alone.
If the source sheet came from CSV, check the imported text and identifiers before blaming the formula. Arabic text looks broken in Excel CSV? Import it as UTF-8
Tools for this task
Before you act
Work on a copy. Do not paste private customer rows into an AI service, enable macros or open an external workbook merely because a generated answer asks you to. Menu availability varies by Excel version.
What to do next
- Write the calculation rule in plain language, including how blanks and rounding should work.
- Create three small test rows with known expected answers.
- Enter the proposed formula on the test sheet and compare every result.
- Copy it down one row and inspect which cell references moved.
- Use Excel’s formula auditing where available; move it to the real sheet only after the checks pass.
