Using Excel’s Formula Evaluator

The example Excel export templates installed with LIMS use formulas to display LIMS data on a report worksheet. Some of the formulas may be lengthy and difficult to comprehend. If you need to modify one of these formulas, wonder why your formula results in an error, or just want to understand how the formula works, Excel’s formula evaluator is a handy tool.

Understanding how a lengthy formula calculates the final result can be difficult because of nested functions and logical tests. In Excel 2010 or newer, the formula evaluator solves a formula one step at a time allowing you to see how the formula is calculated.

Let’s take a look at an example. If you export a Sample Summary report to the Final Report Example template, you will find this formula to display the result for the first analyte in the first sample in cell N25:

=IF(ISBLANK(INDEX(AnalysisResultFormatted, ROW() – ROW(OneAnalysisResults) + 1)), “”,INDEX(AnalysisResultFormatted, ROW() – ROW(OneAnalysisResults) + 1))

With cell N25 selected, select Evaluate Formula in the Formula Auditing group on the Formulas tab.

Use the Evaluate button to view the value of the underlined portion of the formula. The result will appear in italics.

Continue clicking the Evaluate button to view each underlined step in the formula’s calculation and its italicized result. If there is a result for the first analyte in the first sample (i.e. cell N25 is not blank) the final evaluation step in the formula will appear as shown below.

After fully evaluating the original lengthy formula, we can see the formula has been reduced to a simple IF function in the form IF(condition, result_if_true, result_if_false). Since cell W2 on the AnalysisData sheet does have a value (i.e. there is a result for the first sample’s first analyte), the result of the nested ISBLANK function as the condition portion of the IF function is FALSE. Therefore, the result of the IF function is the value in cell W2 on the AnalysisData sheet.

Whether you are developing a complex formula or just trying to understand an existing formula, use Excel’s formula evaluator to visually break the formula down to a series of simple calculation steps. Add this tool to your Excel arsenal and you will quickly understand and troubleshoot daunting formulas.