Getting a Good Look at a Formula

Trying to understand a formula in Excel brings to mind the dilemma faced by residents of Flatland, the mythical two-dimensional kingdom in Edwin A. Abbot’s satirical 1884 novel, Flatland: A Romance of Many Dimensions. “All beings in Flatland, animate or inanimate, no matter what their form, present to our view the same, or nearly the same, appearance, viz. that of a straight Line. How then can one be distinguished from another, where all appear the same?”

Indeed, all formulas in Excel present nearly the same appearance, namely, that of a linear sequence of symbols. How then can one be distinguished from another?

These dilemmas, in Flatland and in Excel, create serious dangers. In Flatland, “triangular houses were universally forbidden by Law” because their hard-to-discern vertices “might do serious injury to an … absent-minded traveller … running against them.” In Excel, best-practices dictate against dangerous shapes for formulas.  Such laws and practices makes life difficult, both in Flatland and in Excel.

Flatland residents must learn to distinguish shapes using senses other than sight. They “distinguish by the voice … the Equilateral, the Square, and the Pentagon.” However, for more complicated figures, “the process of discriminating and being discriminated by hearing increases in difficulty.” Thus they resort to their “sense of touch,” which, “stimulated by necessity, and developed by long training, enables [them] to distinguish angles.”

Excel users must similarly learn by long training to distinguish correct from incorrect formulas by indirect means—by checking them with sample data, by using the Evaluate Formula dialog box, by turning error checking on, by tracing precedents and descendents, … How much easier it would be if Flatland residents could just see shapes and Excel users could just see formulas!

The narrator of Flatland has an epiphany when he visits three-dimensional Spaceland: “I looked, and, behold, a new world! There stood before me, visibly incorporate, all that I had before inferred, conjectured, dreamed.”

Excel users stuck in its one-dimensional formula bar can have a similar epiphany by visiting another dimension—one with which they are already familiar. Who, after all, doesn’t find it easier to recognize the quadratic formula or the normal distribution function when presented in ordinary two-dimensional mathematical notation rather than as one-dimensional sequences of symbols?

(-B3+SQRT(B3^2-4*A3*C3))/(2*A3) 1/SQRT(2*PI())*EXP(-A2^2/2)
Quadratic2 Normal2

And who, when trying to understand why Excel produces an unexpectedly large value for the right-hand formula when A2 is 3, wouldn’t like to see a picture

NormalTree
which shows that Excel evaluates -A2^2 as (-A2)^2 rather than as -(A2^2), as mathematically trained users would expect?

Just as Flatlanders need to see where they are going, less they be pierced by hard-to-discern sharp angles, so formula writers need to see where they are going, lest their users be pierced by unseen errors in formulas. Although Flatlanders are stuck in two dimensions, Excel users need not be stuck in one. FormulaView and FormulaForge both exploit two dimensions to make it easy to recognize and understand formulas. Their math view typesets formulas, and their tree view shows a formula’s hierarchical structure. You can try the math view on-line or download a free copy of FormulaView by visiting http://formulaforge.com/formulaview. Try it, and escape the tyranny of one dimension!