LOOKUP function bug? Using numbers only with an addition formula.

[Guest]

I have a dataset of 325 rows, and 2 columns. All are numbers. I am using LOOKUP to find the value in the 2nd column that matches the lookup value in the same row of the 1st column

Value to search for: =2.11+0.01 (this is in cell AF165

Lookup function: =LOOKUP(AF165,$CB$5:$CB$330,$GY$5:$GY$330

Range CB5:CB330 contains manually typed in numbers in ascending order from 0 to 2.2
Range GY5:GY330 contains numbers calculated by formulas, also in ascending order

The correct value that should be returned (by looking up 2.12) is 197.3, but I get the result of the previous cell: 196.4

When I replace the formula in cell AF165 by manually typing in the number 2.12, I get the correct result of 197.3. I have been able to create a work-around formula to catch this problem, but I would like to know if there is a bug that can be fixed, or a newer version of Excel that I can use, or just a simple problem that I have created myself. I am using Excel 2000 (SP-3) on a Windows2000 Professional Compaq computer

Thankyou for any help.

 

[Harlan Grove]

I have a dataset of 325 rows, and 2 columns. All are numbers. I am using
LOOKUP to find the value in the 2nd column that matches the lookup value
in the same row of the 1st column.

Value to search for: =2.11+0.01 (this is in cell AF165)

You're not really entering a formula like this in AF165, are you? If you're
using a formula that adds 0.01 to a more complicated expression than 2.11, it's
possible that expression doesn't actually evaluate to 2.11, but perhaps to
2.109953781623 (wild guess, don't expect it to hold).

Lookup function: =LOOKUP(AF165,$CB$5:$CB$330,$GY$5:$GY$330)

Range CB5:CB330 contains manually typed in numbers in ascending order from 0
to 2.25 Range GY5:GY330 contains numbers calculated by formulas, also in
ascending order.

The correct value that should be returned (by looking up 2.12) is 197.3, but I
get the result of the previous cell: 196.4.

...

This is almost certainly a case of floating point rounding error. Since you're
using LOOKUP, the simplest way to ensure you get the value you want is either to
use =ROUND(2.11+0.01,2) or =2.11+0.01+0.005 in AF165.

 

[Guest]

You're not really entering a formula like this in AF165, are you? If you'r

using a formula that adds 0.01 to a more complicated expression than 2.11, it'
possible that expression doesn't actually evaluate to 2.11, but perhaps t
2.109953781623 (wild guess, don't expect it to hold)

Thanks for the reply. I actually have an IF statement in cell AF165, but left that out for simplicity. The problem I'm having is still there with or without the IF statement. But the number that I'm looking up (2.11) is a manually entered number to two decimal points. To double check, I extended out the view of the number as far as Excel would permit (127th decimal point). It still did not show any other numerals beyond the 2.11 (other than zeros)

This is almost certainly a case of floating point rounding error. Since you'r
using LOOKUP, the simplest way to ensure you get the value you want is either t
use =ROUND(2.11+0.01,2) or =2.11+0.01+0.005 in AF165

Mysteriously, the ROUND function does get rid of my lookup problem. I can't add an additional 0.005 because I'm using the AF165 value for another calculation.

But the same bug still seems to be there even with the ROUND. If I use the same lookup function as before, I get 197.3 by using "=ROUND(2.11+0.01,2)" and I get 196.4 by using "=2.11+0.01". So it appears that Excel considers "ROUND(2.11+0.01,2)" and "2.11+0.01" to be different numbers. Not comforting

 

[Harlan Grove]

...
...

But the same bug still seems to be there even with the ROUND. If I use the
same lookup function as before, I get 197.3 by using "=ROUND(2.11+0.01,2)" and
I get 196.4 by using "=2.11+0.01". So it appears that Excel considers
"ROUND(2.11+0.01,2)" and "2.11+0.01" to be different numbers. . . .

This is typical for floating point. This is *NOT* a bug. It's standard binary
digital computing, and this 'problem' has been recognized and accomodated by
people who've been doing numerical programming since at least the early 1950s.
Get used to it.