SOLVED

Error while calculating 64,1-0,1 - VLOOKUP

Copper Contributor

Hello!

I've noticed that VLOOKUP formula doesn't work properly while calculating 64,1-0,1. I've made a small example to demonstrate this issue.  

 

There are numbers 63; 64; 63,1; 64,1 in column A. They are associated with numbers 1,2,3,4 in column B. If I want to use function VLOOKUP in column D to associate numbers from column C with numbers in column B everything works fine. 

Funny thing happens if you want to associate numbers from column F with numbers in column B with VLOOKUP function as they are a result of a simple operation: number from column C - 0,1.

The result of this simple operation 64,10 - 0,1 = 64,00 is in the cell F5. I've even checked if the result is the same using formula in F7 (=F5=A3). 

Why do I get an error in G5? VLOOKUP function works perfectly fine in G4, but It does not give a correct result in G5. 

If you change number 64 to 100 in A2 and 64,10 to 100,1 in C5 everything works fine.

There must be some king of a bug in calculating 64,10 - 0,10.

 

How can I fix this issue? This error appears in Office 365 and 2016. File format xls or xlsx doesn't matter. 

7 Replies

@radlek 

 

This is odd. I recreated your table in a new workbook and get the same result. The problem also occurs with the number 4. The lookup works fine if your formula in F is rounded to 1 decimal like "=ROUND(............., 1)

 

But, of course, it should not be like that.

 

@radlek 

That's floating point error. 64 and =64.1-0.1 are differ somewhere in 16th or 17th digit. Usually Excel takes into account first 15 digits, that's why you have TRUE compare two cells. But VLOOKUP checks all 17 digits. Here https://sfmagazine.com/post-entry/august-2017-excel-rank-countif-and-floating-point-errors/ is bit different case, but idea is explained in more details.

 

Such errors appear from time to time when you do math with 0.1 (sure, not only in such cases) due to nature of binary representation of it.

 

The workaround is to use something like =ROUND(64.1-0.1,15)

@Sergei Baklan 

Good catch. Going back to the original file this works, too:

 

=VLOOKUP(INT(F5),$A$2:$B$5,2,FALSE)

best response confirmed by radlek (Copper Contributor)
Solution

@Patrick2788 

Yah, if we assume that lookup value shall be only integer. In general it's ROUND(..,0)

@Sergei Baklan 

Thank you for your help. I read some articles regarding floating point error but in my example even if you check 16th or 17th digit still you get exactly 64. Maybe the difference stays hidden.

64,1-0,1=64,0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
1,2-1,1=0,0999999999999999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

@radlek 

Excel by default works with 15 digits and round the rest. Only few algorithms in Excel calculation engine support 17 digits precision, but you will never see 16th and 17th digit in user interface, zero instead. Majority of functions also ignore 16th and 17th digit.

 

We may consider that as the bug, but that's the one which most probably never will be fixed. From this point of view same one as leap year for 1900. That's what we shall take as it is.

@Sergei Baklan 

Looking it up as an integer is the most likely of the two scenarios.  I think if you need to lookup a decimal that large then the question is - what are you trying to accomplish?  It's not very common.

1 best response

Accepted Solutions
best response confirmed by radlek (Copper Contributor)
Solution

@Patrick2788 

Yah, if we assume that lookup value shall be only integer. In general it's ROUND(..,0)

View solution in original post