MS Office Forum / Excel / Worksheet Functions / February 2008
Odd VLOOKUP result.
|
|
Thread rating:  |
C-L - 11 Feb 2008 19:18 GMT In Excel 2007:
I've noticed that the VLOOKUP and the equivalent INDEX & MATCH functions treat .7 + .1 as something less than .8
Example:
Given the Range...
60% A 70% B 80% C 90% D
The formulas... =VLOOKUP(.7,Range,2) returns B =VLOOKUP(.8,Range,2) returns C =VLOOKUP(.8+.1,Range,2) Returns D as they all should.
However, the formula... =VLOOKUP(.7+.1,Range,2) returns B. or, =VLOOKUP(.7+.1,Range,2,false) returns #N/A.
Certianly work arounds are possible, however it would seem that this is an error on the part of Excel. Can anyone verify? How would one submit a bug report to MS if it's even possible?
Niek Otten - 11 Feb 2008 20:08 GMT Use
=VLOOKUP(.7,Range,2,FALSE)
This is all explained in HELP; the 4th argument controls exact match or approximation
 Signature Kind regards,
Niek Otten Microsoft MVP - Excel
| In Excel 2007: | [quoted text clipped - 24 lines] | error on the part of Excel. Can anyone verify? How would one submit a bug | report to MS if it's even possible? C-L - 11 Feb 2008 20:19 GMT The point is that excel doesn't recognize that .7+.1 = .8 for the purposes of the VLOOKUP function.
> Use > [quoted text clipped - 33 lines] > bug > | report to MS if it's even possible? Tyro - 11 Feb 2008 20:27 GMT It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the two cells together in D3 with =D1+D2, you get 0.8. Extending all three cells to 20 places of decimal shows trailing 0's after the 0.7, 0.1 and 0.8. Enter 0.8 in D4. Extend to 20 places of decimal and there are trailing 0's after the 0.8.
=VLOOKUP(D3,Range,2) returns B. =VLOOKUP(D4,Range,2) returns C =D3=D4 returns TRUE
Something is not kosher.
Tyro
> Use > [quoted text clipped - 33 lines] > bug > | report to MS if it's even possible? Tyro - 11 Feb 2008 20:40 GMT Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6, =0=D5 returned TRUE
Tyro
> It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the > two cells together in D3 with =D1+D2, you get 0.8. Extending all three [quoted text clipped - 47 lines] >> bug >> | report to MS if it's even possible? C-L - 11 Feb 2008 20:51 GMT Thanks for the validation Tyro.
I see the same behavior with respect .7+.1=.8=TRUE.
It seems as though the problem only manifests in VLOOKUP and INDEX MATCH.
> Just to follow up. In D5 I put =D3-D4 and got 0 as the result. In D6, > =0=D5 returned TRUE [quoted text clipped - 52 lines] >>> bug >>> | report to MS if it's even possible? T. Valko - 11 Feb 2008 22:19 GMT This is not a bug in Excel. Search the archives for "rounding issues" by author Jerry Lewis. He explains this in great detail.
As far as 0.7 + 0.1, try this:
A1 = 0.7 B1 = 0.1
=(A1+B1)-0.8=0
The result will be FALSE. This is due to a very small "rounding error". If you have Excel 2002 or later use formula auditing tools>evaluate formula and you'll see what is happening.
 Signature Biff Microsoft Excel MVP
> Thanks for the validation Tyro. > [quoted text clipped - 58 lines] >>>> a bug >>>> | report to MS if it's even possible? Tyro - 11 Feb 2008 22:37 GMT We know there is a discrepancy somewhere.
A1: 0.7 A2: 0.1 A3: =A1+A2 = 0.8 A4: = 0.8 A5: =A4-A3=0 = FALSE A6: =A4=A3 = TRUE
Please explain that to me in great detail. You cannot have it both ways.
Tyro
> This is not a bug in Excel. Search the archives for "rounding issues" by > author Jerry Lewis. He explains this in great detail. [quoted text clipped - 72 lines] >>>>> a bug >>>>> | report to MS if it's even possible? T. Valko - 11 Feb 2008 22:46 GMT Search the archives for "rounding issues" by author Jerry Lewis.
 Signature Biff Microsoft Excel MVP
> We know there is a discrepancy somewhere. > [quoted text clipped - 86 lines] >>>>>> submit a bug >>>>>> | report to MS if it's even possible? Tyro - 11 Feb 2008 22:59 GMT I am very familiar with floating point numbers. If your life depended on TRUE or FALSE, if I choose A6, you live. A5 you die. They should both produce either TRUE or FALSE. Two equal things, as A6 shows, should produce 0 when subtracted, as A5 does not. No way around that. There is either equality or there is not.
Tyro
> Search the archives for "rounding issues" by author Jerry Lewis. > [quoted text clipped - 88 lines] >>>>>>> submit a bug >>>>>>> | report to MS if it's even possible? T. Valko - 11 Feb 2008 23:13 GMT I'll defer to the PhD expertise of Mr. Lewis.
 Signature Biff Microsoft Excel MVP
>I am very familiar with floating point numbers. If your life depended on >TRUE or FALSE, if I choose A6, you live. A5 you die. They should both [quoted text clipped - 96 lines] >>>>>>>> submit a bug >>>>>>>> | report to MS if it's even possible? Tyro - 11 Feb 2008 23:38 GMT It is still an Excel issue. Excel is saying that two numbers, regardless of rounding issues, when compared, are equal and when one is subtracted from the other the result is not 0 - they are not equal. Excel is contradicting itself. There may be hardware issues involved as to how the hardware does comparisons as opposed to arithmetic. But, if so, Excel should take care of that. How are we to trust Excel's tests for equality or subtraction, as the example we have been looking at clearly shows? I wouldn't bet my life on Excel!
Tyro
> I'll defer to the PhD expertise of Mr. Lewis. > [quoted text clipped - 99 lines] >>>>>>>>> submit a bug >>>>>>>>> | report to MS if it's even possible? Tyro - 12 Feb 2008 00:53 GMT Sorry, misspoke. Not rounding issues, but representation issues.
> It is still an Excel issue. Excel is saying that two numbers, regardless > of rounding issues, when compared, are equal and when one is subtracted [quoted text clipped - 110 lines] >>>>>>>>>> submit a bug >>>>>>>>>> | report to MS if it's even possible? Jerry W. Lewis - 14 Feb 2008 05:20 GMT Consider a hypothetical 4-decimal-place computer: 1/3 + 1/3 would then be calculated as 0.3333 + 0.3333 = 0.6666. Mathematically, the answer should be 2/3, but numerically the answer is correctly not equal to 0.6667 because of the impact of finite precision.
In the same way for this calculation, A3 and A4 are correctly not equal to each other. None of the numbers involved have exact binary representations, and so the approximations to the original numbers result in the following calculation:
0.6999999999999999555910790149937383830547332763671875 +0.09999999999999997779553950749686919152736663818359375 ------------------------------------------------------- 0.79999999999999993338661852249060757458209991455078125 which is not equal to 0.8000000000000000444089209850062616169452667236328125
=(A4-A3) returns 1.11022302462516E-16 which is the correct 15-digit (Excel's documented display limit) display of the correct difference of 1.1102230246251565404236316680908203125E-16 (which you can verify if you care to do the math).
AFAIK, Excel's basic arithmetic is done in hardware, not software, and so would give the same result produced by any other software package that does not attempt to re-invent the wheel. Valid criticisms of Excel deal with algorithm choice for calculations beyond basic arithmetic.
The inconsistent comparisons that you got are the result of MS muddying the waters by vainly trying to "take care of that" as you requested. Since Excel 97, final subtractions of numbers that are equal to 15 decimal digits are arbitrarily zeroed under the assumption that the very small difference is unwanted residue of finite precision approximations. If such a subtraction is not the final operation, then the actual difference is retained, since it would reduce accuracy if the assumption were wrong. As a result, =(A4-A3) and =A4-A3 do not return the same value, because surrounding parentheses mean that the subtraction is not the final operation, and therefore the fuzz factor is not applied. Similarly, =A4=A3 returns TRUE because the comparison is the final operation, but =A4-A3=0 compares an unfuzzed (not final subtraction) to 0 and correctly returns FALSE.
I consider this "optimization" http://support.microsoft.com/kb/78113 to be a vain attempt, because no attempt to "take care of that" can succeed in general. Whatever the level of fuzzing, it will be too much for some calculations and not enough for others. Where it is too much, it will reduce the accuracy of calculations. Instead, users need to be aware of the limitations of finite precision arithmetic and do comparisons intelligently. Instead of asking if A3=A4, ask if ABS(A3-A4)<epsilon, where epsilon is a number that is appropriately small for the calculation (this has been taught in programming courses for over half a century). An equivalent approach would be to either round both numbers or the result to an appropriate level. Given that all of the posted numbers have no more than one decimal place, with only adding/subtracting, then rounding to one decimal place would be perfectly reasonable.
Jerry
> It is still an Excel issue. Excel is saying that two numbers, regardless of > rounding issues, when compared, are equal and when one is subtracted from [quoted text clipped - 6 lines] > > Tyro Tyro - 14 Feb 2008 06:47 GMT You don't get it.
Excel has to produce the same results. Two values, one in A1 and one in A2. The formula =A1-A2=0 produces FALSE meaning that A1 is not equal to A2. The formula =A1=A2 produces TRUE meaning that A1 is equal to A2. The representation of numbers is meaningless. Excel is saying that FALSE is equal to TRUE
Tyro
> Consider a hypothetical 4-decimal-place computer: 1/3 + 1/3 would then be > calculated as 0.3333 + 0.3333 = 0.6666. Mathematically, the answer should [quoted text clipped - 84 lines] >> >> Tyro Jerry W. Lewis - 15 Feb 2008 03:43 GMT Numerically, the correct answer is FALSE in both instances, as I explained.
Presumably you want TRUE in both instances, and I explained the well known approach you should take to get that numerically in finite precision. Taking that approach would also avoid this inconsistency.
I agreed that attempting to smooth over the impact of finite precision calculations was a bad idea ("vain attempt") that can produce inconsistent results that are harder to explain than the mathematical inevitability that they were attempting to "fix". However, since they haven't removed their "optimization" after 4 subsequent Excel versions and 11 years of valid criticism, including from some of the best names in floating point arithmetic, such as http://www.cs.berkeley.edu/~wkahan/Mindless.pdf I doubt that MS will ever admit that it was a mistake.
The fact remains that Excel's basic arithmetic is identical to what you would get from any other computer program that follows the IEEE 754 standard for double precision (almost all software). The only way this unfortunate "optimization" will hurt you is if you fail to take the precautions that you should take for any floating point comparison in any software package.
Jerry
> You don't get it. > [quoted text clipped - 95 lines] > >> > >> Tyro Jerry W. Lewis - 15 Feb 2008 03:58 GMT Formatting to show 20 decimal places accomplishes nothing, since Excel (as documented) will never display more than 15 significant digits. If you ask for more, Excel merely pads the dsiplay with meaningless zeros. To see what is really going on, use my VBA function D2D from http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
Most terminating decimal fractions are non-terminating binary fractions that can only be approximated (just as 1/3 can only be approximated as a decimal fraction).
The binary approximations to 0.7 + 0.1 produce 0.79999999999999993338661852249060757458209991455078125 which is not equal to the binary approximation to 0.8 0.8000000000000000444089209850062616169452667236328125
If you are only adding/subtracting 1-decimal-place numbers, then rounding the result of calculations to 1 decimal place does no violence to the calculation and avoids such surprises.
Jerry
> It's more than that. If you enter 0.7 in D1 and 0.1 in a D2 and add the two > cells together in D3 with =D1+D2, you get 0.8. Extending all three cells to [quoted text clipped - 46 lines] > > bug > > | report to MS if it's even possible?
|
|
|