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.
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.
Examples of common errors
Err:503 Division by zero
This error is the result of dividing a number by either the number zero (0) or a blank cell. There is a easy way to avoid this type of problem. When it is possible to either have a zero or blank cell displayed, use a conditional function. The figure below depicts division of column B by column C yielding 2 errors arising from a zero and a blank cell showing in column C.
It is very common to find an error such as this arising from a situation where data was not reported or reported incorrectly. When such an occurrence is possible, an IF function can be used to display the data correctly. The formula =IF(C3>0, B3/C3, "No Report") can be entered. The formula is then copied over the remainder of Column D. The meaning of this formula roughly would be: “If C3 is greater than 0, then compute B3 divided by C3, otherwise enter ‘No Report’”.
It is also possible for the last parameter to use double quotes for a blank to be entered, or a different formula with a standardized number being substituted for the lower number. An example of this might be to use the nursing staff in the unit.
#VALUE Non-existent value and #REF! Incorrect references
The non-existent value error is also very common. The most common appearance of this error arises when a user copies a formula over a selected area. When copying, it is typical for the program to increment the represented cells. If you were copying downward from cell B3 the program would automatically substitute the cell B4 into the next lower cell and so on until the end of the copying process. If that next cell contains text or a value that is inappropriate for the formula, then this error may result. The difficulty usually occurs when one or more of the parameters in the formula need to be fixed.
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.
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.
(This tool should not be confused with the www.SpreadsheetDetective.com third party add-in for Excel. The Spreadsheet Detective provides many advanced features for interpreting complex models, while this Calc feature corresponds to Excel's inbuilt audit arrows functionality.)
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. The cause can be seen in the highlighted cells. 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.