11-13-2019 06:19 AM
11-13-2019 06:19 AM
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.
11-13-2019 07:40 AM
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.
11-13-2019 12:26 PM
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)
11-13-2019 11:23 PM
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.
11-14-2019 02:50 AM
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.