Weird issues with decimals

[billyshears]

I am using a couple sum if formulas in my spreadsheet. I have 2 cell totals that are supposed to equal each other and when they dont, they turn red.

It keeps coming up .00000000042 short of each other and I cant figure out why.

I am not multiplying anything, just adding based on certain categories.

Thanks in advance

 

[MARK858]

Possibly is to do with how Excel handles floating point precision.
Have a read of the old article in the link below...

https://support.microsoft.com/en-gb/kb/78113

 

[shg]

Your computer stores numbers in binary floating point format with finite precision, and most decimals (like 0.1) are repeating decimals in binary the same way that 1/3 is in base 10. You should compare with a tolerance:

=if(abs(a2-a1) > 0.00001, this, that)

 

[joeu2004]

I am using a couple sum if formulas in my spreadsheet. I have 2 cell totals that are supposed to equal each other and when they dont, they turn red. It keeps coming up .00000000042 short of each other and I cant figure out why. I am not multiplying anything, just adding based on certain categories.

It would be prudent to post a concrete example. Show 15 significant digits for numbers. Show formulas.

It is not so unusual. For example, =10.12-10-0.12 displays a negative infinitesimal value if the cell is formatted as General.

In general, the remedy is to explicitly round to the precision that you expect to be accurate. For example, =ROUND(10.12-10-0.12,2) returns exactly zero.