When Formulas Give the Wrong Results

Complicated formulas contain many functions and/or operators along with many subexpressions that serve as arguments for those functions or operands for those operators. When such a formula produces the wrong value, there is generally an error in one or more of its subexpressions. (If not, the formula’s principal function or operator must itself be producing the wrong value, in which case it needs to be replaced by the correct function or operator.) Debugging formulas that contain erroneous subexpressions is a matter of finding and fixing them.

FormulaForge provides much more help for doing this than does Excel alone. First, FormulaForge makes it easy to find all subexpressions in a formula: each subexpression is represented by an element in the Tree View, with the top element representing the entire formula. Without FormulaForge, one generally needs to painstakingly count parentheses in Excel’s formula bar to find where subexpressions begin and end.

Second, FormulaForge shows (in red) the values of most subexpressions next to the elements that represent them in the Tree View. The only values not shown in red are those for constants, for which the value is already visible, and those for arrays (such as multi-cell ranges), for which the existence of too many values to display is indicated by an ellipsis (…).

If a formula produces the wrong value, then the incorrect value appears in red next to its top element in the Tree View. To find the cause of this error, it pays to start by examining the children of the top element to see if one (or more) has an incorrect or suspicious value next to it. (If not, the problem is with the function or operator at the top element itself.) Upon finding a problematic child, the user can click it in the Tree View to have FormulaForge highlight the subexpression it represents in all views (the Text and Math Views in addition to the Tree View). Looking at this subexpression may reveal that its principal function or operator causes the error. If not, the user can proceed successively further down the Tree View, looking at each level for an element that has an incorrect or suspicious value and examining the subexpression that produces that value until one reaches an element for which the error is apparent. Debugging a formula in this fashion lets the user concentrate on only the subexpression(s) in error.

Once an error in a subexpression is identified, the user can correct it by drag-and-drop editing in the Tree View, as shown in the Debugging a Formula screencast on this website and which will also be described in a future blog post.

Below is a fairly simple spreadsheet that attempts to compute how inflation increases the monetary value of a loss. The spreadsheet has a problem, though. The values in range B3:B12, which represent the increasing losses, should all be negative. Instead, they are alternately negative and positive.

Alternating-Row-Errors-Spreadsheet

Below is FormulaForge’s Tree View for the formula in cell B4. You can see the value 10200 of the formula in red next to the top element. (FormulaForge does not attempt to copy the number format for the cell from Excel.) This value should be -10200. Examining the children of the top element shows that the value of the first argument of the IF function is false and that the value of the IF function is that of its third argument, which is 10200 and not -10200. The presence of a unary minus operator in this argument appears to be the problem with the formula.

Alternating-Row-Errors-Tree-View-highlighted

 

Removing this unary minus operator yields the following Tree View, which shows the correct value next to the top element.

Alternating-Row-Errors-Corrected-Tree-View

Finally, copying the formula in cell B4 to cells B5:B12 produces the following corrected spreadsheet, in which values in the range B3:B12 no longer alternate between negative and positive.

Alternating-Row-Errors-Corrected-Spreadsheet

Although this example involves a fairly simple spreadsheet, the technique used to debug it can be used to debug very complicated formulas, including those in which the error is buried deeply. Using FormulaForge makes it easy to plumb those depths without getting lost in the shallows.