Introduction

Spreadsheets, and technology in general for that matter, can be fantastic. Until that is, things don’t go as expected. It’s all too easy for an entire month’s productivity gains to be lost when sorting out a single spreadsheet problem. In this series, we will be look at some examples of Excel not doing what we expect and at how to deal with the problems.

In part 1, we started with the basic problem of our numbers not adding up and saw how Excel’s ROUND() function could help.

Precision as displayed

Last time we looked at how we could use the ROUND() function to ensure that our figures not only add up, but look as though they add up. There is a far simpler way to apparently solve our rounding problem, but it comes with a significant warning. As we saw, rounding problems often arise as a result of the values stored in a cell being held at a precision with a greater number of decimal places than the format chosen for the cell displays. Using the ROUND() function takes a value and changes the precision to a set number of decimal places or units, and the format can then be set to display the same number of decimal places.

It might seem like the best solution would be an option that always ensured that all values in a spreadsheet were only held to the same level of precision as the format allocated to their respective cells displays.

The good news is that there is just such an option: Excel, Options, Advanced category, When calculating this workbook: Precision as Displayed. Just turning this option on will irrevocably round all the values in cells throughout the workbook to the same number of decimal places that each cell’s number format displays. There might be situations where turning on Precision as Displayed is an appropriate choice, but it’s vital to understand what the immediate, and future, results for your spreadsheet could be:

As we can see, this immediately solves the rounding problem of our profit share totals (though not the reconciliation of our profit shares to the total profit). However, when you turn the option on, Excel will display the warning: ‘Data will permanently lose accuracy’. This has a whole set of consequences, most of them bad. For example, if you were using currency exchange rates that needed to be held to 6 decimal places, but were held in cells that were only formatted to display 2 decimal places, just turning on the Precision as Displayed option would immediately and irrevocably reduce the accuracy of all calculations that used the exchange rate:

Before Precision as Displayed is turned on:

…and after:

In fact, it’s worse than that. If Precision as Displayed is turned on, a change in number format can change the numbers elsewhere in a spreadsheet. Changing the number format back will not restore the lost precision. Here, with Precision as Displayed turned on, we reduce the number of decimal places of our VAT rate cell from 2 to 0, then back to 2 again. We’ve used the Excel ‘Camera’ feature to show the effect on our invoice sheet after each change:

Perhaps it’s no surprise that Precision as Displayed has so often been described as being more dangerous than a pint glass full of wasps in a dark pub garden on a warm September evening…

Next time

In the next part of this series, we see why 42 might not always be the answer to life, the universe and everything.

Additional resources

You can explore all aspects of Excel, including the Excel camera, in the ICAEW archive:

• Excel archive