Repeatable debugging

Formula Audit Checklist

When a spreadsheet answer looks wrong, the worst move is to change several parts of the formula at once. Use one known row, write down the answer you expect, and work through this checklist in order. You can usually find the first bad assumption before you touch the full expression.

Start with one known test case

Choose a small row where you already know the correct answer. For example, put quantity 3 in A2, unit price 80 in B2 and discount 10% in C2. The expected net amount is 216.

=ROUND(A2*B2*(1-C2),2)

If the formula does not return 216, do not add error handling yet. Use the steps below to find which part first stops matching the expected result.

1. Formula shell

• Does the expression begin with =?
• Are parentheses balanced from the inside out?
• Are quoted text values closed correctly?
• Does the argument separator match the spreadsheet locale—comma or semicolon?
• Has copied text introduced smart quotes or unusual punctuation?

A formula that the application refuses to accept is usually a syntax problem. Fix that before investigating workbook logic.

2. Function and name resolution

• Are function names supported by the spreadsheet product and version?
• Are localized function names appropriate for the current language?
• Do named ranges, tables, structured references and custom functions actually exist?
• Could an apparent function name really be a workbook-defined name or an add-in function?

When possible, test the smallest function by itself. A working inner calculation is easier to trust than a large nested expression whose failure source is unclear.

3. References and copy behavior

• Do all referenced sheets, cells and table columns still exist?
• Are absolute references such as $A$2 and relative references such as A2 intentional?
• When the formula is filled down or across, do the references move the way you expect?
• For functions that compare multiple ranges, do the ranges start and end on matching rows?
• Is a literal #REF! already present after deleted rows, columns or sheets?

4. Data types before formatting

A cell can look correct while storing the wrong type. Formatting changes appearance; it does not necessarily convert the underlying value.

• Use ISNUMBER to check values that should be numeric.
• For dates, confirm that arithmetic such as A2+1 behaves like a real date serial.
• Keep imported text in a raw column before cleaning it.
• Check leading/trailing spaces, nonbreaking spaces and look-alike punctuation when lookups fail.
• Decide whether codes such as 00123 are identifiers that must remain text rather than numbers.

5. Test the calculation in pieces

For the sample net-amount formula, verify each intermediate value separately:

The first row that differs from the expected result identifies where to focus. This is usually faster than repeatedly editing the full formula.

6. Exercise edge cases deliberately

• Blank input: should the result be blank, zero, a label or an error?
• Zero: is zero a valid measured value or a missing-data substitute?
• Missing lookup: should it display “Not found”, remain blank or stop the downstream calculation?
• Boundary values: test exactly at thresholds, month ends and first/last included dates.
• Empty arrays or filters: what should happen when no rows qualify?

Do not wrap the entire expression in IFERROR until you understand which errors are expected. Broad error handling can hide broken references and invalid data alongside legitimate missing-match cases.

7. Reconcile the result to source rows

For totals and conditional calculations, identify the exact rows that should be included. Add them manually for one small sample and compare that result with the formula. If the totals differ, inspect criteria, dates, text values and range alignment before changing the function itself.

8. Record the environment before handoff

Note the spreadsheet product, version, language/locale, important named ranges, external links and any add-ins. A formula that works in one environment may require a different separator, translated function name or newer function support elsewhere.

Finish with a controlled retest

After each repair, rerun the original known test plus at least one edge case. Change one thing at a time so you can tell which edit fixed the problem. For mechanical structure warnings, use the Formula Checker; for hard-to-read nesting, use the Formula Formatter; and for locale punctuation, compare with the Separator Converter.

Scope: CellLingo can help inspect formula text and demonstrate reproducible examples, but it cannot verify your workbook data, business rules, named ranges or external dependencies. Validate important calculations in the spreadsheet where they will be used.