This note describes two features, used in Excel, for the step by step evaluation of formulas. In Excel the first of these tools is generally more useful but only the second one has an Open Office.org Calc equivalent.
Modern spreadsheets have auditing tools for tracing the precedent cells of formulas and highlighting the formula cells that depend on the current cell. Such tools are very handy when debugging. Recent versions of Excel also have a tool for evaluating formulas step by step. This is particularly useful when complex formulas do not appear to be returning the correct result. We shall illustrate this tool using the Buggy “NZ Rounding” spreadsheet (XLS 34 KB), more fully described in the Rounding Problem #3 article. In particular we will find out exactly why cell B6, below, is incorrectly evaluating to 24.10 when the answer we would expect is 24.20.
We shall also discuss using the recalculate key, [F9], to calculate expressions within a formula using this example. This is the simple technique that is also available (and arguably better implemented) in Open Office.
Using Excel’s Evaluate Formula Auditing Tool
In Excel 2002/2003, the Evaluate Formula tool is available directly from Tools, Formula Auditing Menu or, probably more usefully, as the 
button on the Formula Auditing toolbar.
We shall demonstrate its use by showing some of the steps when it was used to evaluate the buggy formula in B6 illustrated above. This cell is first selected and then the Evaluate Formula option is invoked. (To try this yourself open the Buggy “NZ Rounding” spreadsheet (XLS 34 KB).
Below is the initial dialog box which shows the first item to be Evaluated underlined.
Although it would happily Evaluate A6 for us suppose we decide to choose the Step In option instead. The Step In option is also available here because we have an “unknown” A6 underlined.
After a Step Out and several Evaluates later we get to the dialog below.
Evaluate this one and the problem becomes obvious.
When 24.16-24.1 is evaluated the result is not exactly 0.06 as expected but instead it is something a teeny bit smaller (0.059999, etc!). This occurs because of the precision issue that we discussed in the Rounding Problem #3 article.
Not surprisingly, 
Evaluating from here eventually leads us to the answer of 24.10. Note that the final screen gives us a Restart option, which is useful when you don’t really understand what you’ve just seen!
We now know why our formula is giving an incorrect answer and we would just be left with figuring out how to fix it (At this point, out of curiosity, we Formula Evaluated B7 which has an analogous expression to the one shown here. The crucial expression in this case is 24.26-24.2. This is not exact either but, as it evaluates to 0.06000...023, it leads to the expected answer of 24.30.).
Using the Recalculate Function Key
Both Excel and Open Office.org Calc (and probably other spreadsheets as well) have a quick way of evaluating expressions with formulas by using the recalculate function key [F9]. We illustrate it here using Excel
Once again we select B6 and then while in formula editing mode, we highlight the expression we want evaluated (A6-ROUNDDOWN (A6,1)) and press [F9].
Once again the problem is immediately obvious.
We can then choose other expressions to evaluate in the same way. A further example is shown below.
This is a really useful “quick and dirty” technique but there is a minor problem in Excel at least. Having evaluated expressions within the formula it is rather easy to accidentally enter the changed formula. Selecting the X to exit prevents this and there is always the undo 
.
If you are an Excel user these two options are well worth adding to your repertoire for debugging formulas.
Open Office users should investigate the [F9] function key option.