Spreadsheet formulas can be hard to get right, even when copied from published definitions, like the one for the familiar bell-shaped curve.
Mathematical definition of bell curve
A straightforward transcription of this formula into a spreadsheet fails to produce the expected curve.
Problematic transcription into Excel
FormulaView, an add-on to Microsoft Excel®, helps diagnose problems like the one in this spreadsheet. To use FormulaView, we select cell
B2 in the spreadsheet and click
View Formula on the
FormulaView tab on Excel’s ribbon.
FormulaView tab on the Excel ribbon
This action causes FormulaView to display three views of the formula in cell
FormulaView views of problematic formula
- The Text View shows the formula’s text, as it appears in the formula bar. Nothing looks wrong here.
- The Math View typesets the formula, making it much easier to read. Nothing looks wrong here either.
- The Tree View shows how Excel decomposes the formula into smaller expressions, and it shows the values of those expressions in red. The top value looks wrong.
We trace the origin of the suspiciously large value by looking at other values lower in the Tree View. We find the problem when we get to the box containing the unary minus sign from
-A2^2: Excel has computed
-A2 and then squared the result. We expected it to square
A2 first, as is customary in ordinary mathematical usage and most programming languages.
Excel decomposition of
We fix the problem by going back to Excel and inserting parentheses around the expression
A2^2 in the formula in cell
B2. This forces Excel to square
A2 first and only then to apply the unary minus operation to the result. Then we copy this corrected formula down from cell
B2 through cell
B32 to get exactly the graph we want.
Corrected transcription into Excel