Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup/Calculating Decimals (XP)

    Attached is a worksheet that contains 5 keyed in decimal values, a sum of those values and a simple lookup of that value with a letter being returned from column two of the lookup table.

    When the value is an exact match it returns the wrong letter or gives #N/A. If the value isn't an exact match it returns the correct letter.
    Now if I add the round function to the sum function and round it out so far that rounding really doesn't occur. The vlookup works perfectly.

    Is there something I am missing as far as how excel adds up raw decimal values.

    Ultimately I have several spreadsheet in which I need to lookup letter grades based on a final numeric grade.

    Thank You,
    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup/Calculating Decimals (XP)

    We use the decimal number system. Excel internally uses the binary number system. Translation back and forth between the two system inevitably involves small rounding errors. In most situations, you won't notice them, but sometimes they affect calculations. Apparently, SUM(A2:E2) results in a number ever so slighty smaller than 70 (even though =(F2=70) results in TRUE).
    One way around it is to round intermediary results.
    In this particular situation, you could also add a tiny amount to the sum, for example in F2:

    =SUM(A2:E2)+0.00000000000001

    This will force the VLOOKUP to return the correct grade.

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup/Calculating Decimals (XP)

    Wow... that is good to know. Thanks so much,
    Steve

Posting Permissions

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