Finding and fixing errors
It is common to find situations where errors are displayed. Even with all the tools available in Calc to help you to enter formulas, making mistakes is easy. Many people find inputting numbers difficult, and many may make a mistake about the kind of entry that a function's argument needs. In addition to correcting errors, you may want to find the cells used in a formula to change their values or to check the answer.
Calc provides three tools for investigating formulas and the cells that they reference: error messages, color coding, and the Detective.
Error messages
The most basic tool is error messages. Error messages display in a formula’s cell or in the Function Wizard instead of the result.
An error message for a formula is usually a three-digit number from 501 to 527, or sometimes an unhelpful piece of text such as NAME?, REF, or VALUE. The error number appears in the cell, and a brief explanation of the error on the right side of the status bar.
Most error messages indicate a problem with how the formula was input, although several indicate that you have run up against a limitation of either Calc or its current settings.
Error messages are not user-friendly, and may intimidate new users. However, they are valuable clues to correcting mistakes. You can find detailed explanations of them in the help, by searching for Error codes in OpenOffice.org Calc. A few of the most common are:
- NAME? (525): No valid reference exists for the argument.
- REF (525): The column, row, or sheet for the referenced cell is missing.
- VALUE (519): The value for one of the arguments is not the type that the argument requires. The value may be entered incorrectly; for example, double-quotation marks may be missing around the value. At other times, a cell or range used may have the wrong format, such as text instead of numbers.
- 509: An operator such as an equals sign is missing from the formula.
- 510: An argument is missing from the formula.
- 502: The column, row, or sheet for the referenced cell is missing.
Color coding for input
Another useful tool when reviewing a formula is the color coding for input. When you select a formula that has already been run, the cells or ranges used for each argument in the formula are outlined in color.
Calc uses eight colors for outlining referenced cells, starting with blue for the first cell, and continuing with red, magenta, green, dark blue, brown, purple and yellow before cycling through the sequence again.
The Detective
In a long or complicated spreadsheet, color coding becomes less useful. In these cases, consider using the the submenu under Tools > Detective. The Detective is a tool for checking which cells are used as arguments by a formula (precedents) and which other formulas it is nested in (dependents), and tracking errors. It can also be used for tracing errors, marking invalid data (that is, information in cells that is not in the proper format for a function's argument), or even for removing precents and dependents.
To use the Detective, selective a cell with a formula, then start the Detective. On the spreadsheet, you will see lines ending in circles to indicate precedents, and lines ending in arrows for dependents. The lines show the flow of information.
Use the Detective to assist in following the precedents referred to in a formula in a cell. By tracing these precedents, you frequently can find the source of the errors. Place the cursor in the cell in question and then choose Tools > Detective > Trace Precedents from the menu bar or press Shift+F7. The figure below shows a simple example of tracing precedents.
Cursor placed in cell |
a) Initiate trace by clicking Trace Precedents |
b) Source area highlighted in Blue, with arrow pointing to the calculation cell |
Tracing precedents using the Detective.
We are concerned that the number shown in Cell C3 is incorrectly stated. Many times the solution to the concern can be seen visually as in our simple example. In this case cell C16 contains both numeric data as well as letters. Removing the letters resolves the problem in the calculation.
In other cases we must trace the error. Use the Trace Error function, found under Tools > Detective > Trace Error, to find the cells that cause the error.
