Round or use Excel’s “Precision as<br>Displayed”

When you use MSC-LIMS’ Excel interface to calculate and import analytical results, you should be aware of the difference between a cell’s actual stored value, and the numbers displayed in the cell. One MSC-LIMS user called us recently with a problem that demonstrates the importance of this difference. A simple example shown in the two Excel screens below demonstrates the problem and its solution.

In the workbook above, cells A2 and B2 have been formatted to display their values with two decimal places. All remaining cells use the default General number format. Cells A1 and B2 have similar formulas to help us see the affects of rounding (the screen below shows the cell formulas).

When LIMS imports the value in cell B2, the result value is similar to the number in cell B1. Given the formula in cell B2, a result of 0.66 was expected.

What happened?

Applying a specific numeric cell format in Excel does not change the cell’s stored value with its default 15-digit numeric precision; it only affects how the value is displayed. The stored values in cells A2 and B2 are the same as the values shown in cells A1 and B1.

The recommended solution to this problem is to use Excel’s ROUND function to obtain the desired result. The formula in cell B3 in the screen above rounds the result of the same quotient to two decimal places, which produces the correct result (0.66).

This is the recommended solution since the ROUND function need only be used within formulas in cells whose results are imported into LIMS. All remaining cells with formulas continue to be calculated with full numeric precision.

However, there are situations such as the caller’s where this solution may be cumbersome. In this case, other intermediate calculations were also affected. A number of columns had been formatted to display the proper number of decimal places and the resulting rounding was not producing the expected results. If Excel would only perform its calculations with the cell’s displayed value instead of the full precision of the stored value the problem would be solved. Excel’s “Precision as displayed” feature was the solution.

To force Excel to perform its calculations with the numeric precision of the displayed value, use Tools | Options then select the “Precision as displayed” option on the Calculations tab. When you select this option, Excel changes the stored value in a cell from full 15-digit precision to the format displayed.

Using the “Precision as displayed” feature in our example above, the stored and displayed value in cell B2 will automatically change to 0.66. Note that selecting this option will affect all cells in the workbook.