I have this worksheet where I need to create a checker to determine whether a number (result of dividing the sum of two numbers by another value --DIVISOR) is an integer/does not have decimals. Upon running the said checker, it mostly worked just fine but appeared to detect that a few items are not integers despite being exact multiples of the DIVISOR.

https://docs.google.com/spreadsheets/d/17-idS5G0kUI7JoHAx3qcJOiJ-zofmMrg93hUvZuxPiA/edit#gid=0

I have two values (V1 and V2) whose sum I need to divide by a certain number (Divisor).

I need the OUTPUT to be an integer/whole number. Since the DIVISOR is a multiple of SUM (V1,V2), the OUTPUT is supposed to be a whole number. I also expanded the number of decimal places to make sure that there are no trailing numbers after the decimal point.

However, upon running the MOD function over the OUTPUT, it generated some infinitesimal value.

I also tried TRUNCATING the OUTPUT and getting the DIFFERENCE between the TRUNC and OUTPUT. It yielded the same remainder value as the MOD result.

I downloaded the GSheet and opened it in MS Excel. There seems to be no problem with the DIFFERENCE result, but the MOD function yielded yet another value.

CodePudding user response：

actually, this is not a bug and it is pretty common. its called a floating point "error" and in a nutshell, it has to do things with how decimal numbers are stored within a google sheets (even excel or any other app)

more details can be found here: https://en.wikipedia.org/wiki/IEEE_754

to counter it you will need to introduce rounding like:

=ROUND(SUM(A1:A))

this is not an ideal solution for all cases so depending on your requirements you may need to use these instead of ROUND:

ROUNDUP
ROUNDDOWN
TRUNC
TEXT