# Thread: Vlookup/Calculating Decimals (XP)

1. ## 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. ## 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. ## 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
•