match odd behaviour with 0.1

[nparslow]

Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC ok, make a little table:

0 0.1 0.2

ask office to match 0.1 in the table, it should come up with '2' (i.e. the second number is 0.1)

now get it to match (4.1-4) instead, if you're the same as me, it will come up with '1' instead of '2'

really bizarrely, 1.1-1, 2.1-2, 3.1-3 will all work fine, numbers four or greater fail. Same result for MOD(4.1,1) or 4.1-INT(4.1), and same if the numbers are cell references.

I'm on office 2004 on a powerpc mac 10.4.11

I also tried 'hlookup' and had the same problem.

If you do matchtype = 0, you get a #N/A error for all situations like 1.1-0.1, but not for 0.1 without any calculations.

I'm assuming this is a bug, but I haven't found anywhere to report it. (Support website tells me to come back later).

any thoughts on if I'm doing something wrong?
thanks for any help you can give.

 

[JE McGimpsey]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Power PC

ok, make a little table: <br><br>0 0.1 0.2 <br><br>ask office to match 0.1 in
the table, it should come up with '2' (i.e. the second number is 0.1)
<br><br>now get it to match (4.1-4) instead, if you're the same as me, it
will come up with '1' instead of '2' <br><br>really bizarrely, 1.1-1, 2.1-2,
3.1-3 will all work fine, numbers four or greater fail. Same result for
MOD(4.1,1) or 4.1-INT(4.1), and same if the numbers are cell references.
<br><br>I'm on office 2004 on a powerpc mac 10.4.11 <br><br>I also tried
'hlookup' and had the same problem. <br><br>If you do matchtype = 0, you get
a #N/A error for all situations like 1.1-0.1, but not for 0.1 without any
calculations. <br><br>I'm assuming this is a bug, but I haven't found
anywhere to report it. (Support website tells me to come back later).
<br><br>any thoughts on if I'm doing something wrong? <br>
thanks for any help you can give.

No bug. Simply the limits of double precision floating point
representation (as in nearly any spreadsheet application).

XL only carries precision to about 15 decimal digits, and internally
rounds beyond that.

Just as most real numbers (e.g., 1/3) cannot be exactly represented in
a finite number of decimal digits (think 0.3333333333333333....), so
most numbers cannot be exactly represented in 64 binary bits.

When you do math on fractional values, there is often a small rounding
error.

Try =MATCH(ROUND(4.1-4,1),A:A) and see what happens.

 

[nparslow]

ah thanks,
so I just tried ROUND(4.1-4,1) and that works fine, so you're spot on.
thanks for the fast reply!