Why is Excel telling me the GCD of 1.13*100 and 100 is 4?

[SamRoss]

In Microsoft Excel, if I type in the formula =GCD(113,100) then it gives me the correct answer of 1. However, if I type in =GCD(1.13*100,100), which means the same thing, it tells me 4. What's going on and how can I fix it? Thanks

 

[jedishrfu]

I think it’s due to the 1.13*100 being treated as a float ie it’s not the same as the integer value 113.

You could test this by using the int() function on 1.13*100 to see if that fixes it.

 

[SamRoss]

I think it’s due to the 1.13*100 being treated as a float ie it’s not the same as the integer value 113.

You could test this by using the int() function on 1.13*100 to see if that fixes it.

That fixed it. Thanks!

 

[Mark44]

In Microsoft Excel, if I type in the formula =GCD(113,100) then it gives me the correct answer of 1. However, if I type in =GCD(1.13*100,100), which means the same thing, it tells me 4. What's going on and how can I fix it? Thanks

Here's the documentation for the gcd function in Excel:

GCD(number1, [number2], ...)
The GCD function syntax has the following arguments:

• Number1, number2, ... Number1 is required, subsequent numbers are optional. 1 to 255 values. If any value is not an integer, it is truncated.

Due to the way that floating point numbers are stored, the value 1.13*100 turns out to be a little less than 113. This is true of computers in general, and isn't some bug in Excel. You can verify this by the formula

-(1.13*100 - 113)*10^6

The actual difference between 1.13*100 and 113 is so small that it is displayed as 0, but if you multiply this difference by, say, 10^6, you will get -1.42109*10^(-8).
Since 1.13*100 < 113, according to the docs above, it is truncated, leaving the integer value 112. Certainly gcd(112, 100) = 4

 

[Tom.G]

For the messy details see:
http://binary-system.base-conversio...ent=01111100&mantissa=00001010001111010111000
above found with: https://www.google.com/search?&q=convert+0.13+to+binary