Trace a Wrong Result with Detective in OpenOffice Calc

Use Detective to follow the cells feeding a suspicious result before editing the result itself. A formula can calculate correctly from the wrong input; arrows reveal dependencies, not whether the underlying business facts are true.

Make a reproducible mistake

Use Apache OpenOffice Calc 4.1 desktop with English menus. This is a documentation-based exercise, not a native-tested repair. Work in a disposable ODS file and keep a saved copy before altering formulas.

Create this table in A1:D4:

Item	Units	Price	Revenue
Notebooks	10	4	=B2*C2
Pens	20	2	=B3*C3
Folders	5	6	=B4*C4

In C6 type Total and in D6 enter =SUM(D2:D4). In C7 type Reserve and in D7 enter =D6*0.1. The correct line revenues are 40, 40 and 30; total 110; reserve 11.

Now deliberately replace C3 with 20. The pen price is wrong by a factor of ten. Revenue becomes 400, total becomes 470 and reserve 47. There is no formula error code because all expressions are syntactically valid.

Follow the dependency chain

1. Select D7, the reserve that first drew your attention. Choose Tools > Detective > Trace Precedents. On Windows/Linux, Shift+F7 is the documented shortcut.
2. Read the actual formula in the input line. D7 points to D6; it is applying 10% as designed. Do not overwrite D7 with the expected number 11.
3. Select D6 and trace its precedents. Check that SUM includes D2:D4, all three line revenues, and no unrelated cells.
4. Select D3 and trace its precedents. Inspect B3 and C3 against the known fixture. Twenty units is correct; a price of 20 is not.
5. Correct C3 to 2. Confirm D3=40, D6=110 and D7=11.

Tracing each selected formula separately makes the chain explicit. It also distinguishes a bad input from a bad range. If D6 were =SUM(D2:D3), it would omit Folders even with the correct pen price. In that case the repair belongs in the SUM range, not in C3.

Check the effect in the other direction

Select C3 and choose Tools > Detective > Trace Dependents to identify where the input is used directly. D3 should be a direct dependent. Continue from D3 to D6, then from D6 to D7. Following the chain step by step avoids assuming a single arrow display reveals every downstream calculation.

Change C3 to 3 as a controlled test. Expected results are D3=60, D6=130, and D7=13. Restore C3 to 2 afterward. These independently calculated values prove what should change and what should remain fixed: D2=40 and D4=30 throughout.

Detective also has Tools > Detective > Trace Error for cells with calculation errors. That is a different diagnostic from this exercise’s plausible but wrong number. Do not expect Trace Error to identify a price typo that is valid numeric data. If a cell displays a formula rather than calculating, first use the formula-text repair guide.

Clean up without destroying evidence

Use the Detective menu’s removal commands to remove tracing marks when finished; do not delete source cells merely to clear arrows. Save the corrected workbook separately from the intentionally wrong example if you need an audit trail.

Close and reopen the corrected ODS file. Verify the formulas remain formulas and the values are again 40, 40, 30, 110 and 11. A successful numeric result is not enough if someone replaced the dependency chain with constants. On a real workbook, confirm suspicious values against an authorized source and document the correction instead of guessing what “looks right.”

Official reference

Apache OpenOffice 4.1 Calc Guide, Chapter 7, Detective and tracing errors. Recovered guide retrieved 19 October 2026. The expected arithmetic is independently checked; native arrow appearance and save/reopen behavior remain unverified.