Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    vlookup cant do decimals (excel 2000)

    I have attached a workbook which does the following:

    1 enter in two distances per row (c3:d22) input page
    2 c27 choices lite or stadard this will bring up a different value on a lookup table
    3 below c35:J55 is a table which use vooklups to fill in based on the choice with an overrite highlite in green.

    the vlookup looks at the values in column h which is the differences between the 2 distances enter on each row mentiones in # 1.

    to make the code a cell smaller and maintainable i built two more tables in column l to column n and col p to R which do the actual lookups.

    my issue is if the difference between two distances in 1.4 the vlookup does not work it only works if the difference value in a nice round number

    anyone have a solution to my issue

    thanks
    markus

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vlookup cant do decimals (excel 2000)

    You are having a problem with rounding errors. Computers work in binary, and not all decimal numbers convert exactly to binary. If you change the format of those cells to display 15 digits after the decimal, you will see that the result in those cells are not exactly 1.4, they will be something like 1.3999999999998. When you display only one digit after the decimal, it rounds the display to 1.4, but the vlookup is done on 1.3999999999998, and those results do not always match. You need to change the formulas in E2:E22 to be something like:

    <code>
    =ROUND(D4-C4,1)
    </code>

    and change the formulas in H40:H58 to something like:

    <code>
    =IF(I40=0,ROUND(D4-C4,1),I40)
    </code>
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: vlookup cant do decimals (excel 2000)

    Thanks for the advice i never thought of the vlookup depending on binary like that

    I used the ex. VLOOKUP(H123,LiteCalcsList,3,True) to get around it but your advice has correctly fixed it

    Thanks Again!! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •