How to Evaluate Excel Formulas Incrementally

Whether you are trying to solve a cell’s #NAME? or #VALUE! error or you just want to understand a lengthy cell formula, Excel’s ‘Evaluate Formula’ feature is a very helpful tool. Let’s look at a couple of examples.

The ‘CofA Example.xltm’ example template has the formula =INDEX(SampleID,1,1) in cell H9 on the CofA worksheet. That cell and others show the #NAME? error. If you don’t already know why the error appears, select the ‘Evaluate Formula’ option in the Formula Auditing group on Excel’s Formulas tab and the formula will appear in the popup screen shown above.

The ‘Evaluate Formula’ screen is a handy tool to watch the sequential steps Excel uses to calculate the result of the formula. The underlined part of the formula shows the expression that will be calculated and replaced by its result when you click the Evaluate button.

After clicking the Evaluate button, the screen above shows that the ‘SampleID’ name in the formula is causing the #NAME? error. That is expected in an LIMS export template because the named ranges are only created by the new workbook’s macro after the LIMS data has been exported to Excel. The named ranges are created using the LIMS field names on row one of a worksheet containing LIMS data.

Let’s look at a more complex example. The formula below appears in cell D16 of a workbook created by exporting the Sample Summary report to a template and the cell is displaying the #VALUE! error.

=IF(ISERROR(INDEX(AnalysisResultFormatted,

ROW() – ROW(OneAnalysisResults) + 1)), “”,

INDEX(AnalysisResultFormatted,

ROW() – ROW(OneAnalysisResults)))

Note that the template’s ‘Remove Infrastructure Sheets’ option on the Settings worksheet has been disabled so all the infrastructure sheets and their LIMS data are available. When we use the ‘Evaluate Formula’ option the popup begins with the following in the Evaluation field.

After clicking the Evaluate button we can see that the AnalysisResultFormatted named range refers to cells AA2:AA6 on the AnalysisData worksheet as shown below.

After several more clicks of the Evaluate button we can see that the results of the ROW() and ROW(OneAnalysisResults) expressions are both 16:

A couple more evaluation steps and we see the result of the first INDEX expression has retrieved the value of 7.10 from the first cell in the AnalysisResultFormatted named range (i.e. cell AA2 on the AnalysisData sheet):

The result of the ISERROR expression is False:

The next two evaluation steps are about to reveal the problem:

We were expecting the second INDEX expression to retrieve the result from the first cell in the AnalysisResultFormatted named range just like the first INDEX expression in the formula. However, we can see in the evaluation step above we are asking for the 0 th cell. That results in the #VALUE! error:

Incrementally evaluating our formula reveals that the second INDEX expression is missing the “+1” in “ROW() – ROW(OneAnalysisResults) + 1” like the first INDEX expression uses. Correcting that oversight solves this formula’s problems.

Add Excel’s ‘Evaluate Formula’ feature to your troubleshooting tools and you can learn how to create and troubleshoot more complex formulas.