Excel Tip of the Week #244 - Revisiting precision as displayed

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a General User appropriate post in which I'm going to revisit one of the more unusual topics I've ever covered here in the TOTW - Precision as Displayed (PAD).  This was originally covered back in TOTW #41.

What makes this topic unusual is that I am not here to talk about this feature and tell you how and when to use it.  Instead, as I did in the original post, I am going to make the loudest argument I can that you should never use this feature.  You could make an argument that it would be safer if I didn't make you aware of this dangerous option, so you were never tempted to try it, but as the option can sound useful if stumbled across, I think it's worth making the case about just why it's so bad.

What does Precision as Displayed do?

Normally, Excel goes about rounding numbers in two ways.  The numbers stored in Excel's memory are stored with a high degree of precision, i.e. a lot of decimal places, and these are only rounded off if a function like ROUND is used (see TOTW #42 and #45 for more on rounding functions).  Secondly, the numbers that are displayed on your screen are rounded according to their formatting - i.e. presentationally.  This sometimes leads to situations where a range of numbers appears not to correctly add to a total - for example, three formulas of =1/3 might look like 0.33+0.33+0.33, but add to 1.00.  This is what Precision as Displayed changes.

When activated via the options menu (and again, not giving details here as you should not use it), PAD changes things so that however the formatting of numbers is set, that's how they are stored.  So for example a number that's formatted with the Currency preset format will be stored only to two decimal places.  This is an option setting that automatically effects every number in a file.

Why is Precision as Displayed dangerous?

PAD is incredibly dangerous.  First off: PAD is a setting.  That means it's invisible and a new user to the workbook may not realise that their presentational adjustments are actually changing the numbers in the spreadsheet.  What's worse, if a PAD-activated workbook is the first thing you open when you boot up Excel, the setting will spread to any other workbooks you open during that session.

When activated, PAD makes accidentally changing or deleting data very easy.  If you adjust the number of decimals in a range and accidentally overshoot, your data loses accuracy (and you may not even notice).  If your range accidentally extends to a percentage value, you could easily take 1% to 0%.

Even if these kinds of accidental changes don't occur, merely activating PAD will decrease the accuracy of your spreadsheet.  PAD rounds all numbers in a series of calculations at each stage - so unless you have set intermediate calculation steps to use many more decimal places, you will round at every stage - input, calculation, and output - and each instance of rounding will decrease the accuracy of the whole process and the end result.  What's worse, there is no way after the fact to know how big of an effect this had when PAD was first turned on.

Ultimately, while having numbers 'add by eye' might be appealing, PAD creates many more problems than it solves.  Try to avoid rounding until the very last moment, or altogether - precision is more useful than neatness.  Keep your rounding to formatting only.

Finally, because it's too good not to, let me repeat this classic quote on PAD from my fellow blogger, Simon Hurst:

“Precision as Displayed is evil, and its use is likely to cause a global financial catastrophe that would make the last few years seem like a minor, local hiccup.  It’s as dangerous as a pint glass full of wasps in a dark pub garden on a warm September evening.

Thanks and see you next week!

Previous post - VBA case study: Calculating on cells of a given colour
TOTW index 
Next post - Finding the last number in a range

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous
  • I'm a dissenting voice. I use precision as displayed sometimes and I find it useful. Your points about working carefully, to the correct precision and intermediate calculations are well made and understood. If I am working on a budget then the errors in all the assumptions and estimates are far, far greater than any decrease in accuracy caused by working to a set number of decimal places. If I need precision then I make sure I work to higher numbers of decimal places. 

    I find the Round function most useful when I am using a negative number for the second element of the function. It allows me to convert the spurious precision of a budget into something that demonstrates the level of error inherent in the work - such as rounding to the nearest 1,000.