Ceci est une ancienne révision du document !
In my last article, I showed you how to use the Mark Invalid Data tool in Calc's Detective tools. This time, I am going to discuss the rest of the tools in the Detective menu that help us trace the cells used in formulas. Through these tools, we can learn what cells affect the result of a formula, what formulas a cell affects, and what cells are involved in a formula that results in an error. The sheet we will use is small to help you understand how they work, but these tools are most helpful in a large sheets with complicated formulas.
Setting Up the Sheet
Create a new, blank sheet. In cell A1, enter the value 13. Select cells A1:A6. Edit > Fill > Series to bring up the Fill Series dialog. For the Direction, select Down, and for the Series type, select AutoFill. Click OK, and the cells are filled with sequential numbers. Select cells A1:D6. Edit > Fill > Series. This time, select Right for the Direction, but again select AutoFill for the Series type. Click OK and the cells to the right are filled with sequential numbers.
Create totals for the rows and columns by selecting the blank cell at the end of the row or column. Click the SUM button on the formula toolbar. The cells to the left for rows, and above for the columns, are automatically selected. Press the Enter / Return key on the keyboard to accept the results. Repeat for each of the rows and columns.
Cell E7 will sum the totals of both the rows and the columns. The formula is
=SUM(A7:D7,E1:E6)
Trace Precedents
The Trace Precedents function (bottom right) shows the cells used by the formula in the current cell. Traces are displayed on the sheet with blue arrows and blue frames around cell ranges. This is true for all the trace functions.
Select cell E7. Tools > Detective > Trace Precedents. Blue arrows and frames show the cells used by the formula. If you entered the formula correctly, this includes all the totals for the rows and columns. To clear the trace markings off the sheet, select Tools > Detective > Remove Precedents.
Trace Dependents
Trace Dependents (next page, top left) draws arrows to the cells containing formulas that rely on the currently active cell. If you select C4 then Tools > Detective > Trace Dependents, you get arrows pointing to C7 and E4. Both of these cells contains formulas that reference C4 in their range. Just as with the Trace Precedents, the ranges of the two formulas are surrounded by a blue box. To clear the trace markers, Tools > Detective > Remove Dependents.
Common Calc Errors
Before we talk about Trace Error, let's take a quick look at some common errors you will see in Calc.
#DIV0! – The formula is trying to divide a number by zero. As we learned in elementary mathematics a long time ago, you can't divide a number by nothing. Some functions, like VARP and STDEV, will give this error when you fail to use the minimum required arguments.
#REF – The cell or range reference is invalid. This mostly happens when you mistype your reference or you reference a cell or range that no longer exists. For example you have a reference to a sheet that has been deleted.
#VALUE – The input was a type other than what was expected. This is often caused by referencing a cell with text in a mathematical function or formula.
Err:510 – A variable is missing from the formula. This can happen when you type two operators together, like =1 +* 3.
Trace Error
Trace Error draws trace arrows to all the precedent cells which caused an error in the currently selected cell's formula. To demonstrate this, we will need to generate an error. #DIV0 is probably the easiest to generate. Enter the formula =C3-D2 in cell E8. The result is zero. In cell E9 enter the formula =E7/E9. This will generate a #DIV0 error.
To trace the error, select cell E9. Tools > Detective > Trace Error. A trace arrow is drawn through the cells involved in the formula. In our case, it is just two cells. You can change the minus to a plus and the arrow will clear and the error will go away, but for now, let's leave it the way it is. I have one more thing I want to show you. The only way to clear an error trace without correcting the error is Tools > Detective > Remove All Traces.
Combining Traces
Tracking down a problem or an error is not always as simple as just using one of the trace tools. You might need to combine them to find the real issue. Let's take our error for example. We can select the cell E9, and run the error trace, and see that the formula uses the two cells above it. However, once you realize that both cells contain formulas as well, you can trace the precedents on cell E8 to see if you can find the problem. You then realize that the two cells used in the formula are the same value. Then it hits you; you meant to use C6 – not C3. Change C3 to C6 in the formula in E8, and the error and the error trace arrow go away. Also note that the precedents traces move with the change of the formula.
With the detective's trace tools in Calc, you can trace the cells used in a formula and determine why you didn't get the results you expected. Trace Precedents shows you the cells used by the formula in the current cell. Use Trace Dependents to show the cells containing formulas that use the currently selected cell. If you are trying to determine why a formula is generating an error in the current cell, you use the Trace Error tool. Finally, you can combine the different trace tools to get a broader view of what is happening, or not happening, in your sheet.