Have I discovered a bug? - Counting number of decimal places

[caffeine_demon]

Hi,

So - I wanted to find a way of counting the number of decimal places in a number, so my idea was:
- Subtract the "int" part of a number from the number. (Ie a1-int(a1) ) - giving just the decimal value
- use "len" to calculate the length of that, and subtract 2 (for the 0, and the decimal point) - (so - Len(a1-int(a1)) - 2 )

I was getting strange results, if we start with :
A1 = 2.05
A2 = Int(a1)
A3 = A1-A2
a4 = len(a3) -2

a2 results in "2" - as expected, a3 shows "0.05" - as expected, but a4 is saying the length is 16. Going into the formula wizard, it tells me the value of A3 is "0.49999999999989" (there may be more 9s)

trying values from 2 up to 4 (in steps of 0.01) - it appears only 2, 3 and 4 give the correct result

Also - if I just enter the formula "Len(2.05-2) -2" , that also gives 16.

Is there something I've missed, or have I found a bug? is there a better way to calculate the number of decimal places?

(This is office pro 365 at work)..

Thanks.

 

[joeu2004]

Anyone remember the pentium bug?

The Pentium bug affected only the FDIV instruction (non-integer division). It was due to a manual encoding error in a lookup table that was designed to speed up the instruction.

It had nothing to do with the 64-bit binary floating-point limitations that caught you by surprise.

A simpler demonstration of the 64-bit binary floating-point limitation is the fact that IF(10.01 - 10 = 0.01, TRUE) returns FALSE(!).

10.01 appears to be 10.0100000000000 and 0.01 appears to be 0.0100000000000000 when they are formatted to display 16 decimal places (15 significant digits for 0.01).

But 10.01 - 10 appears to be 0.00999999999999979 when formatted to display 17 decimal places (again, 15 significant digits).

The problem might not be so mysterious if Excel did not limit the formatting to 15 significant digits (rounded). The exact decimal representation of the binary values is:

10.01       10.0099999999999,997868371792719699442386627197265625
10.01-10     0.00999999999999978,68371792719699442386627197265625
0.01         0.0100000000000000,0020816681711721685132943093776702880859375

(I use period for the decimal place and comma to demarcate the first 15 significant digits.)