VLOOKUP makes a strange distinction between values and calc results 3

[joerd]

I recently found a strange error using VLOOKUP (actually, MATCH does the same):
[tt]
In cell A6, type 0.1
In cell A7, type =A6+0.1 and copy down to A25
Copy A6:A25 to B6:B25
In cell C6, type =VLOOKUP(B6,A$6:A$25,1,FALSE) and copy down to C25
All looks fine, but now copy A6:A25, and paste the values onto itself (or fill A6:A25 using Fill/Series with a step of 0.1).

My results are:
Source data Lookup Result
0.1 0.1 0.1
0.2 0.2 0.2
0.3 0.3 #N/A
0.4 0.4 0.4
0.5 0.5 0.5
0.6 0.6 0.6
0.7 0.7 0.7
0.8 0.8 #N/A
0.9 0.9 #N/A
1 1 #N/A
1.1 1.1 #N/A
1.2 1.2 1.2
1.3 1.3 1.3
1.4 1.4 #N/A
1.5 1.5 #N/A
1.6 1.6 #N/A
1.7 1.7 #N/A
1.8 1.8 #N/A
1.9 1.9 #N/A
2 2 #N/A

[/tt]
Can anyone explain the strange behavior at 0.3, 0.8, etc.? And hopefully present a solution?
Alternatively, if you omit the FALSE (or use TRUE for range_lookup), you will see a wrong result at 0.8, 0.9, 1.0 and 1.1
( I am running Excel 2003 (11.8307.8221) SP3)

Regards,

Joerd

 

[SkipVought]

Hi,

Is this ACTAULLY causing a problem?

I do not have a definitive answer, but often floating point arithmetic can cause inconsistent results. Why there would be a difference between the literal, 0.3 and the calculated cell in column B, for instance, could only be explained if one knew if formulas and literal numeric values were handled differently in the c# program behind the workbook.

This sounds to me like a classroom assignment, though, and I return to my opening question.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

For instance, you can mitigate the effect of the inexactness like this...
[tt]
=VLOOKUP(INT(B6*10)/10,A$6:A$25,1,FALSE)
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joerd]

Yes, it's causing a problem, and no, I'm not a computer science student. The problem I presented is not my actual case, but representative of the error.
The problem is that it's easy to miss the error, especially if you're doing a range lookup, and then continue working with the wrong answer - also, or especially, in real life!
Functions like MATCH and VLOOKUP should be robust, and never give wrong answers when used as specified.

Regards,

Joerd

 

[AnotherHiggins]

BTW: I'm able to duplicate your results in Excel 2003, but this does not happen in Excel 2007.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

As Skip pointed out, this sounds like a floating point problem.

You can get around these problems as follows (instructions are for 2003 and earlier, which you must have since, as I said, 2007 doesn't act as you describe):

Go to Tools > Options > Calculation and check the box beside Precision as displayed.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hey, part of playing in the sandbox, is understanding the limitations of computers. That's just what happens with FLOAT. Hence the arithmetic handstands to get the 'correct' answer.

So if you think you've got a problem, then work with it or around it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joerd]

Thanks John, should've thought about that myself. Turns out that (at least in Excel 2003):
(0.2+0.1-0.3) equals 5.6E-17 and not 0

Would have been nice if this had been fixed before 2007, but we're all learning. The same functionality that allows 5.6E-17 to be displayed as 0 could have been used in the lookup functions.

Also, for engineering calcs, using precision as displayed is typically not what I want to do.

Anyway, learned something new today, even though I was aware of the issue in a different context.

Regards,

Joerd

 

[Gavona]

=(0.2+0.1-0.3) equals 5.6E-17 and not 0

but =0.2+0.1-0.3 equals 0 hard to see why brackets make the difference!

The underlying problem is that the base 10 numbers we use cannot all be precisely represented in Base 2 (which computers use).

http://support.microsoft.com/kb/78113

Gavin

 

[Gavona]

joerd, if precision is importnt to you then this my be of interest:

In

http://blogs.msdn.com/excel/archive...es-excel-give-me-seemingly-wrong-answers.aspx

Greg Lovern said:

My Excel add-in, xlPrecision, has been resolving these problems for Excel users since 2003. See

http://precisioncalc.com/xlprecision.html

and

http://precisioncalc.com/Testimonials.html.

BTW I am a former MS Excel product support escalation lead at Microsoft.

Gavin

 

[joerd]

@Gavona

but =0.2+0.1-0.3 equals 0 hard to see why brackets make the difference!

Just multiply that 0 result by 1E10 or so, and see the answer. Thanks for the link, btw.


Regards,

Joerd

 

[Gavona]

joerd,
Sorry I don't see your point. Answer I get is 0 without the brackets and 5.55112E-07 with the brackets.

Have fun,

Gavin

Gavin

 

[joerd]

You're right. That's awesome!

Regards,

Joerd

 

[lionelhill]

I could suggest a reason, but it may be rubbish: somewhere, somehow, perhaps even as deep as the processor's floating point hardware, the calculation is originally being stored at a higher precision than is used for "intermediate results", and somehow, Excel is exploiting the full precision when it converts the number to a displayable decimal. The brackets are making Excel store the results of the expression inside the brackets as an intermediate result, and it's losing a tiny bit of precision in so doing.

There's been some writing here of "fixing" this error in different versions of Excel. It's not an "error", and any "fixing" is merely aesthetics based on assumptions. As Gavona wrote, no matter how hard you try, many decimal numbers cannot be represented exactly in binary. At some point there will be a loss of precision. If you try hard enough, you can expose it (avoiding doing so is the point of looking at numerical stability in numerical methods).

If Microsoft were to modify a comparison in floating point so that instead of testing "equals zero" it tests "difference is less than the smallest precision error I expect to make", then someone, somewhere, would generate a result that differs by that precision, and report that Excel has a "bug" because it reports two visibly different numbers as equal.

The answer is to be aware that floating point is rarely exact, and even Microsoft can't make it so.