Excel System Error???
 

I have a simple vlookup function that works sometimes and not other times.
Please check this out for me. I am totally lost on this one. I use excel
2002. Try this out...

A1: =14%
B1: =A1+0.01
C1: =VLOOKUP(B1,D:E,2,FALSE)
D1: =15%
D2: =16%
E1: =3%
E2: =4%

I get an error #N/A on cell C1. However if I change B1 to be =A1+.02, the
formula works. Also, if I just type in 15% into B1 it works.....confused?

Excel System Error???
 

I have no clue why but if you change the formula in cell B1 to be something
like the following it should work. (I guess you can adjust the # of digits
to round by)

=ROUND(A1+0.01,2)

Hope this helps.

Thanks,
Bill Horton

"JJ" wrote:

I have a simple vlookup function that works sometimes and not other times.
Please check this out for me. I am totally lost on this one. I use excel
2002. Try this out...

A1: =14%
B1: =A1+0.01
C1: =VLOOKUP(B1,D:E,2,FALSE)
D1: =15%
D2: =16%
E1: =3%
E2: =4%

I get an error #N/A on cell C1. However if I change B1 to be =A1+.02, the
formula works. Also, if I just type in 15% into B1 it works.....confused?

Excel System Error???
 

This is a binary rounding problem. Excel (most computer apps) use the IEEE
convention which allows about a 15 decimal digit accuracy. With this lime
some decimal values cannot be EXACTLY represented in binary. When you use
ROUND you overcome the roundoff error
best wishes and Merry Christmas (Ignore if offended)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JJ" wrote in message
...
I have a simple vlookup function that works sometimes and not other times.
Please check this out for me. I am totally lost on this one. I use excel
2002. Try this out...

A1: =14%
B1: =A1+0.01
C1: =VLOOKUP(B1,D:E,2,FALSE)
D1: =15%
D2: =16%
E1: =3%
E2: =4%

I get an error #N/A on cell C1. However if I change B1 to be =A1+.02, the
formula works. Also, if I just type in 15% into B1 it works.....confused?

Excel System Error???
 

If you want to see the internal representation of these numbers more
precisely, try my functions from
http://groups.google.com/group/micro...fb95785d1eaff5

Specifically, you will find that 14%+0.01 does not have the same internal
representation as 15%, since none of these 3 decimal fractions have exact
binary representations (just as 1/3 has no exact decimal representation).

You cannot directly see the difference, because Excel will only display 15
digits, but you can see it by subtraction,
=(14%+0.01-15%)
returns 2.77555756156289E-17, which is the difference that VLOOKUP is
recognizing.

Jerry

"JJ" wrote:

I have a simple vlookup function that works sometimes and not other times.
Please check this out for me. I am totally lost on this one. I use excel
2002. Try this out...

A1: =14%
B1: =A1+0.01
C1: =VLOOKUP(B1,D:E,2,FALSE)
D1: =15%
D2: =16%
E1: =3%
E2: =4%

I get an error #N/A on cell C1. However if I change B1 to be =A1+.02, the
formula works. Also, if I just type in 15% into B1 it works.....confused?