The Value of Multiple, Synchronized Views

Different Excel users create different kinds of formulas. Some users create mathematical formulas, which are the Excel equivalent of formulas that appear in mathematics textbooks and articles; such formulas use functions (e.g., PI, EXP, and SQRT) and operators (+, -, etc.) to compute mathematical quantities. Some users create algorithmic formulas, which are the Excel equivalent of computer programs; such formulas use other functions (e.g., IF, CHOOSE, and SUMIF) to carry out branching and looping computations. And some users create formulas that contain both mathematical and algorithmic elements.

Our primary goal in developing FormulaForge was to help Excel users understand and construct all kinds formulas. As Steve shows in his blog post, Getting a Good Look at a Formula, it is easier to understand mathematical formulas when they are typeset mathematically. Similarly, it is easier to understand algorithmic formulas when they are displayed in a tree that reveals their algorithmic structure. For this reason, FormulaForge shows three views of each formulas; a Text View, a Math View, and a Tree View.

Here is how Formula Forge displays the quadratic formula. Although well-known by mathematicians, it is nonetheless complicated.

Quadratic-Formula

One can see the text view of the formula in the Text View pane in much the same form as in the Excel formula bar. Although the formula is primarily a computation, it is difficult to discern what that computation is simply by looking at the Text View. The Math View, however, shows the formula in much the same form as it would appear in a mathematics text book. In this view, it is clear what the overall computation is, and for users with a mathematics background, it is clearly the quadratic formula. Even for formulas that don’t correspond to well-known formulas, the Math View can make the computation more clear than the simple Text View.

One way to understand complicated formulas is to examine their constituent subexpressions, which are easily selected using FormulaForge. Clicking anywhere in the Text View or in the Math View selects the smallest subexpression that contains the selection point. Clicking any element in the Tree View selects the smallest subexpression containing that element. The following image shows the result of selecting the subexpression corresponding to the SQRT function.

Quadratic-Formula-with-Highlighted-SQRT

By selecting this subexpression, the user can focus attention on understanding the value of the SQRT function in this formula and how that value is computed from the subexpressions that compose the argument to the function. It does not matter how the user selects this subexpression: FormulaForge synchronizes the resulting views by highlighting the selected subexpression in each of the views. FormulaForge also guarantees that, no matter how the subexpression was selected, it is a complete subexpression. Excel itself neither makes any such guarantees nor makes it easy for users to select entire subexpressions. In Excel users can select the text “SQRT(B2“ even though that text doesn’t represent a complete subexpression, and Excel provides no assistance in expanding that selection into a complete subexpression. As a result, selection in FormulaForge is much more useful than selection in Excel for understanding how formulas work.

FormulaForge’s synchronized display of formulas in different views allows the user to approach understanding and debugging formulas at two different levels. At a high level, the Math or Tree View may reveal details that are easy to miss in the standard Excel Text View but much more apparent in the overall views they provide of a formula. Large-scale structural problems or purely mathematical mistakes may become evident at this level. For example, deeply nested parentheses in the Text View may have hidden the fact that a factor belonging in the numerator of a fraction actually wound up in the denominator. At a lower level, the breakdown of a large formula into its constituent subexpressions, each with its value visible in the Tree View, can quickly point out how an error in one part of the formula has resulted in the wrong value for the entire formula. In these ways, multiple views, together with synchronized highlighting of subexpressions, provide a very powerful tool not just for understanding complex formulas but also for debugging them.