Place the cursor in the cell in question and then choose Tools > Detective > Trace Precedents from the menu bar or press Shift+F7. By tracing these precedents, you frequently can find the source of the errors. Use the Detective to assist in following the precedents referred to in a formula in a cell. On the spreadsheet, you will see lines ending in circles to indicate precedents, and lines ending in arrows for dependents. To use the Detective, selective a cell with a formula, then start the Detective. The Spreadsheet Detective provides many advanced features for interpreting complex models, while this Calc feature corresponds to Excel's inbuilt audit arrows functionality.) (This tool should not be confused with the third party add-in for Excel. 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. 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. In these cases, consider using the the submenu under Tools > Detective. In a long or complicated spreadsheet, color coding becomes less useful. 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.Ĭalc 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 that way, the program will carry that name to each succeeding formula being copied and remove the need to use the $ to anchor the reference to the TotalExpenses cell.Īnother useful tool when reviewing a formula is the color coding for input. To avoid the #VALUE and #REF! errors, give the cell B3 a name such as TotalExpenses. ![]() 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. When it is possible to either have a zero or blank cell displayed, use a conditional function. There is a easy way to avoid this type of problem. This error is the result of dividing a number by either the number zero (0) or a blank cell. 502: The column, row, or sheet for the referenced cell is missing.Įxamples of common errors Err:503 Division by zero.510: An argument is missing from the formula.509: An operator such as an equals sign is missing from the formula.At other times, a cell or range used may have the wrong format, such as text instead of numbers. The value may be entered incorrectly for example, double-quotation marks may be missing around the value. VALUE (519): The value for one of the arguments is not the type that the argument requires.REF (525): The column, row, or sheet for the referenced cell is missing.NAME? (525): No valid reference exists for the argument.You can find detailed explanations of them in the help, by searching for Error codes in Calc. However, they are valuable clues to correcting mistakes. 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.Įrror messages are not user-friendly, and may intimidate new users. The error number appears in the cell, and a brief explanation of the error on the right side of the status bar. ![]() Error messages display in a formula’s cell or in the Function Wizard instead of the result.Īn 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |