0

I am trying to find the result of 692.79-692.45 using Excel, but find the following issue:

enter image description here

enter image description here

enter image description here

692.79 and 692.45 are exact numbers with only two digits, but the result is not 0.34. Is there any solution to this problem? Thanks for any help.

Yang Yang
  • 858
  • 3
  • 26
  • 49
  • 3
    I am not getting that, are you sure the actual value is `692.79` and `692.45` and have no decimals beyond that are formatted not to be seen. – Scott Craner Apr 02 '20 at 19:51
  • @ScottCraner Yes, I have double-checked the two numbers, they do not have any more decimals. – Yang Yang Apr 02 '20 at 20:09
  • Have you tried to simply input the values in A1 and B1 manually? You can also try "=ROUND(A1,2)-ROUND(B1,2) and see if that corrects the issue. If either of these work, then you need to look at the source for the numbers you are using and make sure that you aren't dealing with the floating point issue Tarik pointed out. – Frank Ball Apr 02 '20 at 20:15
  • @FrankBall I input A1 and B1 manually (shown in the new figures). If I ROUND C1, I will get the correct result. I wonder how this problem occurs? Thanks. – Yang Yang Apr 02 '20 at 20:17
  • Possible duplicate of [Is floating point math broken?](https://stackoverflow.com/q/588004/11683) – GSerg Apr 02 '20 at 20:29
  • What's the source of the numbers? – Frank Ball Apr 02 '20 at 20:30
  • @FrankBall They are just some random numbers I use to show the problem. – Yang Yang Apr 02 '20 at 21:18
  • 2
    See [Floating-point arithmetic may give inaccurate results in Excel](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result) for explanation and work-arounds. And there are many other articles addressing this issue. – Ron Rosenfeld Apr 03 '20 at 01:20

2 Answers2

1

Floating point numbers are stored in binary. The translation from decimal to binary causes rounding errors.

See https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html for more details.

Tarik
  • 10,810
  • 2
  • 26
  • 40
1

There are two potential problems:

  1. A1 or B1 may be formatted to display only 2 decimal digits
  2. C1 may be subject to rounding errors

In either case you could use:

=ROUND(A1-B1,2)

enter image description here

EDIT#1:

To verify A1 and B1, pick two other cells and enter:

=A1=ROUND(A1,2)
=B1=ROUND(B1,2)

If either formula reports FALSE, you have uncovered the bad data.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99