Excel troubleshooting guide

Why your Excel IF formula returns 0

A zero is usually a clue, not the cause. Use these six checks to find whether Excel chose the false branch, compared unlike values, moved a reference, skipped recalculation or hid an error.

A five-minute diagnostic sequence

  1. Replace the IF outputs temporarily with labels such as "TRUE branch" and "FALSE branch".
  2. Inspect the exact values and data types used by the condition.
  3. Compare the formula references in one working row and one failing row.
  4. Recalculate the workbook and check calculation mode.
  5. Temporarily remove broad error handling such as IFERROR(...,0).

Work on a copy if the workbook is operationally important.

  1. Confirm that zero is not the intended false result

    In =IF(C2="Paid",D2*E2,0), Excel must return zero whenever the test is false. Make the branch visible before changing the condition:

    =IF(C2="Paid","TRUE branch","FALSE branch")

  2. Compare text after ordinary spaces are normalized

    "Paid" and "Paid " are different text. A controlled comparison can normalize case and ordinary spaces:

    =IF(UPPER(TRIM(C2))="PAID",D2*E2,0)

    Important: Microsoft documents that TRIM does not remove the nonbreaking space character 160, which commonly arrives with copied web text. Do not assume TRIM cleans every invisible character.

  3. Check whether a number is stored as text

    A cell that looks like 100 may still be text. Inspect Excel's warning indicator, try ISNUMBER(A2), and convert only after confirming the intended decimal and date conventions. Changing display formatting alone does not necessarily convert the underlying value.

  4. Inspect references in the failing row

    Excel updates relative references when formulas are copied. If a tax rate or lookup table must remain fixed, the intended reference may need dollar signs:

    =D2*E2*$H$1

    Compare one working formula with the failing formula; do not add absolute references everywhere.

  5. Recalculate and verify calculation mode

    Excel normally calculates automatically, but Manual mode can leave old results visible. Recalculate and check the workbook's calculation setting. Microsoft also documents stale-value formatting for cells that have not yet been recalculated.

  6. Stop replacing every error with zero

    A formula such as =IFERROR(XLOOKUP(...),0) makes a missing key look like a valid zero. During diagnosis, expose the underlying error or return a specific message:

    =IFNA(XLOOKUP(A2,Rates!A:A,Rates!B:B),"Missing rate")

    This distinguishes “not found” from a legitimate rate of zero.

Run a private first pass

Paste one formula into the free browser-only checker. It checks common maintenance risks without uploading the formula. Continue with the CAD 5 downloadable workflow, or get a written second opinion for CAD 9.

Open the free formula checkerDownload the workbook — CAD 5Get a one-formula review — CAD 9

Primary references

This guide explains general troubleshooting patterns. It does not inspect your workbook and is not financial, tax or accounting advice.