Excel Tips and Tricks #447 – Rounding Errors Revisited

Rounding errors

Before we explain what causes them, let's first identify what we mean by a 'rounding error'. They are easy to spot - for example if you expand the answers to some simple arithmetic out to a large number of decimal places:

Or sometimes when you are looking at the total value that comes out of a PivotTable - here we have a trial balance that in theory should sum to zero:

The number here is in scientific notation - it represents -0.00000000094769. Obviously these errors are both tiny, but why do they occur?

The answer relates to how Excel handles numbers. Like many other programs, Excel works on a ‘floating-point’ basis – in simple terms, it adjusts the precision of the number it stores to minimise space and maximise calculation speed, but means that sometimes calculations can go slightly awry as there’s a fractional difference between how the number is stored, and how the number is displayed. The reason is because Excel (and indeed all computers) count in binary, not decimal. Numbers like 0.1 are easy to express in decimal, but are essentially impossible to express in binary - just like how one-third can't be represented exactly in decimal, and instead we have to approximate with 0.3333333...

If you’d like to understand more on storing decimal numbers in binary, take a look at this article.

Excel has a bunch of clever tricks in it to prevent these kinds of errors, but sometimes - particularly when working with numbers that are exact in decimal, or with very large or small numbers, or with lots of numbers like in a PivotTable - these methods fail. That's why you'll sometimes see these tiny errors creeping in.

What to do about floating point errors

Most of the time, the right answer is 'nothing'.

Floating point errors are by their very nature small - they occur after the fifteenth decimal place. In most cases, just change the cell formatting to show a limited number of decimal places, and don't worry about it. The errors are so tiny that they won't affect subsequent calculations at all.

There is one particular case however that can make floating point errors more annoying, however: testing for equivalence. Going back to our PivotTable, let's say we have an error-checking formula that makes sure the trial balance sums to zero:

This is particularly frustrating here, because we have updated the formatting to 2 decimal places, so by eye everything looks fine - and yet still the error check returns a false positive.

One solution is to round numbers - see TOTW #252 for more details - but this isn't always appropriate as rounding introduces a loss of precision that's likely to be greater than the underlying floating point error was in the first place. 

Similarly, users can change the preferences for a workbook to ensure that Excel calculates based on the displayed precision. If you go to File>Options>Advanced, you’ll find the following check box towards the bottom of the screen:

This means that Excel will only store numbers to the level of precision that you can see on screen. It’s another risky strategy as accuracy beyond that is simply not saved, which could again lead to a loss of precision.

A better way is to build a small degree of tolerance into error checks such as this one:

This also helps with avoiding other false positives caused by inexact calculations eg translating amounts between different currencies.

It’s worth noting at this point that Excel is not unique in having these sorts of issues. Many programs will manifest calculations like this in different ways, and will solve them to varying degrees of success. Alteryx, for example, has exactly the same issue with floating point values, and the solution is broadly similar – force calculations to a set number of decimal places, or build tolerances in your comparisons.