Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel bug?
Excel 2000 SP3 fully updated.
I'm going to get out of my mind. I would appreciate any concern to an issue I am facing with lookup functions: My table array is $A$2:$B$3 with A2 = 0,00 B2 = 0,00% A3 = 2.000,01 B3 = 0,30%. Now, in another cell, say A6, I type 2.500,10 and in B6 500,09. In C6 =A6-B6 ( the difference is 2.000,01). In cell C7 =VLOOKUP(C6;$A$2:$B$3;2;TRUE). The result is 0,30%, which is correct. Another scenario now: A6=2.849,10 and B6=849,09. VLOOKUP returns 0,00%, which is wrong. Working around to this issue I found out that it comes up only when the numbers in A6 and B6 are greater thun 2.511,10 και 511,09 respectively, the difference is 2.000,01 (i.e. equal to A3) and the decimals 01, 10 and 09. No other decimals cause this problem. Also, the thousnads digit in numbers A3 and A6 must be 1, 2 or 3. From 4 and above (say 4.000,01 and 4.849,10) everything is OK. Similar problem appears if I use =INDEX($A$2:$B$3;MATCH(C6;$A$2:$A$3;0);2) instead of VLOOKUP, regardless of decimals in numbers in A6 and B6 in this case. Is it a bug or am I doing something wrong? |
#2
|
|||
|
|||
it's probably not a bug, per se, but rather because of small internal
rounding errors (see http://cpearson.com/excel/rounding.htm for a detailed explanation). I can replicate your results in XL04. This formula can be used as a workaround: =VLOOKUP(ROUND(C6;2);$A$2:$B$3;2;TRUE) In article , "AndreasN" wrote: Is it a bug or am I doing something wrong? |
#3
|
|||
|
|||
Thank you very much for your explanation. Please let me investigate this
matter a little further. I made the following test: I pasted A6:C7 two lines below, in A9:C10. Next I created two different instances as follows: A6 = 2.500,10 B6 = 500,09 C6 = 2.000,01 C7 = 0,30% A9 = 2,849,10 B9 = 849,09 C9 = 2.000,01 C10 = 0,00% According to C. Pearson's explanations the values in C6 and C9 appear to be same, but actually they are not, due to background small rounding errors. My question now is why do I get TRUE if I compare these values in another cell (i.e. =C6=C9) instead of FALSE, as one would normally expected? Thank you again. Ο "JE McGimpsey" έγραψε στο μήνυμα ... it's probably not a bug, per se, but rather because of small internal rounding errors (see http://cpearson.com/excel/rounding.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |