0

Doing some calculation I have 3 sums from around 15 lines, one of them should be the sum of the two others.

To be sure that my job is ok, I test Sum1 - Sum2 - Sum3.

Until know it worked perfectly, but today it was not 0, but something very small. so I rounded the calculation and I still have a non-zero result.

I tried to put the number in their plain values in 3 cells and did the sum again, still non-zero.

My numbers are : 232863.86, 229305.16, 3558.7

When calculating 232863.86 - 229305.16 - 3558.7 the result is -1.72804E-11

I've seen Excel giving non-zero result on complicated calculation but never on something that small. Does anyone know what is going on ?

There is abslutely no emergency, just curiosity.

Have a good day.

Edit 1:

Hi, Subject was closed as a duplicate but i do not think this applies: - First i'm not using VBA - Second i'm aware that there are some issues when working with big numbers or numbers with a lot of decimals, which is not the case here. The three numbers are supposed to be below the number of caracters or decimals

Delen 22
  • 1
  • 1
  • Hi, Subject was closed as a duplicate but i do not think this applies: - First i'm not using VBA - Second i'm aware that there are some issues when working with big numbers or numbers with a lot of decimals, which is not the case here. The three numbers are supposed to be below the number of caracters or decimals – Delen 22 Nov 09 '20 at 16:02
  • 1
    The info contained in the dup applies to both Excel and VBA. Also, the info applies to _all_ numbers, not just "big" numbers. Did you read the linked paper? It fully explains what's going on – chris neilsen Nov 11 '20 at 20:09

1 Answers1

0

The calculation is OK. If you want to round to 0 if it really small under a number:

=IF(ABS(A1)<N,0,A1)

Where N is your threshold and A1 is the cell.

Wasif
  • 14,755
  • 3
  • 14
  • 34