It’s not mentioned a lot, but some of Excel’s subtraction calculations are not exact. When you look at them at 15 decimal points they are slightly out. Most times this make no measurable difference to the result. But sometimes when you round, it does.

Look at the image below – it all looks ok. Column D shows the formula in column C.

It gets interesting when you increase the number of decimal points in cell C2.

As you can see when you get to around 14 decimal places we see some issues. In most cases this won’t be an issue.

Let’s add rounding and another calculation and see what happens.

As you can see the rounded result in cell C3 is wrong. 2.34 times 0.25 equals .585 and should be rounded up to .59 as per cell C4.

The reason can be seen in the image below where I selected the multiplication part of the calculation and pressed function key F9 to display the result.

This is slightly less than 0.585 and hence is rounded down to .585.

Floating Point Precision

This is caused because computers store numbers using binary. When you uses 0’s and 1’s to store numbers, at certain levels of precision the accuracy is not exact and we see results as per above. The error is known as a Floating Point Precision (FPP) error.

Solution

In our case we can solve the error by adding an extra ROUND to the formula – see image below.

It was the subtraction result that generated the error, so we need to round its result.

Addition

It doesn’t appear to affect addition as much as subtraction – see image below.