1

I have this values in this cells:

  • I7: 100000

  • I8: -99500.34

  • I9: -103

  • I22: =ROUND(SUM(I7:I21);2)

  • J5: 396.66

  • K22: =IF(J5=I22;"OK";"NO")

Format of all cells are numeric with two decimals.

In this case, the K22 cell shows OK, it is correct because the cells of the comparasion have the same value.

However, if in the cell I22 I don't use ROUND, then it doesn't work:

  • I22: =SUMA(I7:I21)

In this case, K22 shows "NO".

Why is the result "NO"?

I have realized that if I don't use ROUND but I use another values in the cells, it works.

This case works.

  • I7: 100000

  • I8: 10

  • I9: 10

  • I22: =SUM(I7:I21)

  • J5: 100020

  • K22: =IF(J5=I22;"OK";"NO")

Why it doesn't work in the case in which it fails?

Thanks.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
Álvaro García
  • 18,114
  • 30
  • 102
  • 193
  • 1
    Read https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result – user11222393 Jun 27 '23 at 11:33
  • In the documentation it is said it affects to Excel 365, Excel 2013 and other versions, but i am using excel 2021. – Álvaro García Jun 27 '23 at 11:47
  • It is true that the documentation tells, it can be solved with ROUND to avoid the problem. But It decoumentation tells that the error happens with value very much small or value very big. It is not my case, but it is true that with ROUND the problem is solved. – Álvaro García Jun 27 '23 at 11:53
  • 1
    I believe most if not all excel versions are based on IEEE754 so problem applies to all versions. Not only "big" or "small" numbers. Example from page: A1: =(43.1-43.2)+1, Instead of displaying 0.9, Excel displays 0.899999999999999. Because (43.1-43.2) is calculated first, -0.1 is stored temporarily and the error from storing -0.1 is introduced into the calculation. And yes, ROUND is good workaround. – user11222393 Jun 27 '23 at 11:58
  • But the 2 solutions are not good enough for me. First, I have to use round everytime to ensure it works. If not, I am not sure if it will work or not. The second solution, precision as displayed, I lost precision, so what happen if I need this precision? I have to use the first solution, that it is to use ROUND everytime. But it is how it works. – Álvaro García Jun 27 '23 at 11:58
  • In your example of A1: =(43.1-43.2)+1 I get 0,9 without rounding. – Álvaro García Jun 27 '23 at 12:05
  • 1
    0.9 displayed doesn't mean actual value is 0.9. Format A1 cell to show 15 decimals or put =(43.1-43.2)+1 in A1 and then =A1=0.9 somewhere. – user11222393 Jun 27 '23 at 12:32
  • 1
    Read this, will enough to know : **[Floating-Point-Math-Broken](https://stackoverflow.com/questions/588004/is-floating-point-math-broken)** – Mayukh Bhattacharya Jun 27 '23 at 12:46

0 Answers0