Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem Opening an Excel File MSO9.DLL Error | Links and Linking in Excel | |||
Excel getting Error - file format is not valid WHEN OPENING | Excel Discussion (Misc queries) | |||
Problem Opening an Excel File MSO9.DLL Error | Links and Linking in Excel | |||
Error message in Excel after exporting Access query to Excel | Excel Discussion (Misc queries) | |||
error opening excel file | Excel Worksheet Functions |