0

Excel is rounding numbers inconsistently that is causing me issues. When using ROUND(), sometimes it rounds a specific number up, while at other times it rounds the same value down.

I've tried setting Excel to show exact values in settings, but it doesn't change anything.

This is an example of what is happening.

This is the simple formula ROUND((A1-B1)/2,4)

For one record I have the values (.3159 - .3152) which evaluate to .0007 then divide by 2 to get .00035.

For the next record I have the values (.3554 - .3547) which also evaluates to .0007 and divided by 2 results in .00035

So, even though both values are .00035 when I round off to 4 decimal places I am getting .0003 for one and .0004 for another. Same number, rounding to the same number of places, two different results. How can I fix this?

jimbob6
  • 1

1 Answers1

1

This is an issue with floating point numbers that is inherent and cannot be solved, only avoided.

Try these tests in Excel:

=(0,3159-0,3152)=(0,3554-0,3547) gives you FALSE.

=(0,3159-0,3152)-(0,3554-0,3547) gives you something like 5.55112E-17.

If you cannot accept the differences, you should round already in the middle of the calculation, not only at the end:

=ROUND(0.3159-0.3152,4)=ROUND(0.3554-0.3547,4) is TRUE

=ROUND(0.3159-0.3152,4)-ROUND(0.3554-0.3547,4) is 0

further reading: Is floating point arithmetic stable? and Binary floating point and .NET, by highly regarded Jon Skeet.

Wolfgang Jacques
  • 769
  • 6
  • 15
  • Microsoft has an [article](https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel) concerning Excel. – Wolfgang Jacques May 01 '19 at 19:21