An example
=IFERROR(#REF!B2*0.2, 0)
The sheet this formula read was deleted. It now refers to nothing, and IFERROR turns that into a plausible 0, for good.
How it happens
When a sheet is deleted, every formula that used it is rewritten to #REF!. Usually the cell then shows #REF! and somebody notices. Wrapped in IFERROR it shows whatever fallback was chosen, often 0 or a blank, and nobody does.
Why it matters
A zero looks like a real answer. A summary built on it is understated, with no error anywhere to say so.
How to find it by hand
Search the workbook for #REF! with Find, Ctrl and F, looking inside formulas rather than values: in Excel, set Look in to Formulas; in Google Sheets, open Find and replace and tick Also search within formulas.
How Proper Spreadsheets finds it
The checker reads the text of every formula, so it finds #REF! used as a reference even where the cell shows a calm number, and names any sheet a formula refers to that is not in the file.
Check a whole file in seconds
Finding this by hand means reading every formula in the file. The free check reads them all, along with six other mistakes that show no error, and points to each cell. Your file is deleted as soon as the check finishes.
Other mistakes worth checking for
- A total that stops short of the data: The formula is fine. It just ends a few rows too early, and nothing on the screen says so.
- A number typed into a formula: It is right until the rate changes. Then it is wrong in every row it was copied into.
- One row that calculates something different: Every row in the column does the same thing, except one. The answer it shows looks perfectly normal.
- A figure typed over a formula: The column calculates, row after row, until the one row where somebody typed the answer in.
- A total that disagrees with its rows: The rows add up to one figure and the total says another.
- Error values, and the one cell causing them: Two hundred cells show #REF!. One of them is the cause, and it is rarely the first one you see.