Forum Discussion
Mysterious bug on excel calculation, on a vlookup
Glarrain wrote: ``A7 and A8 looks like a 100% like the one on A11 and A12``
And looks can be deceiving.
The problem has nothing to do with errant characters (which would result in #VALUE errors, anyway), and using the Excel VALUE or VLOOKUP(...,TRUE) function might work-around the problem only by coincidence.
Instead, this is just yet-another example of binary (floating-point) arithmetic anomalies.
The work-around is: whenever you expect a calculation to be accurate to some number of decimal places, explicitly round to that number of decimal places -- and not to arbitrary number, as some people suggest. For whole percentage values, round to 2 decimal places (because 12% is 0.12). For percentage values that should be accurate to 2 percentage decimal places, round to 4 decimal places (because 12.34% is 0.1234).
It appears that the values in A7 and A8 had been calculated (somewhere), then copy-and-pasted-value into A7 and A8. And the calculated values are infinitesimally smaller (-1.11E-16) than how the values appear, even when formatted to display 15 significant digits. We can see such differences with formulas of the form =SUM(A7,-(A7&"")) formatted as Scientific or General.
(That works because the expression A7&"" returns the value formatted with up to 15 significant digits. That is Excel's arbitrary formatting limit. It is not the limit of numeric precision, as many documents state incorrectly.)
Additionally, VLOOKUP is among the many Excel functions that compare exact binary values, not the values arbitrarily rounded to up to 15 significant digits, as compare operators ("=", ">=", etc) and the *IF[S] functions do (COUNTIF, SUMIFS, etc).
Since the value in A7 is infinitesimally less than the value in A4 (exactly 100%), VLOOKUP(...,TRUE) returns the value in column B (B3) that corresponds to the largest value less than 100%, namely 95% in A3.
Since the (same) value in A8 is infinitesimally different from the value in A4, VLOOKUP(...,FALSE) returns #N/A because it fails to find an exactly binary match.
In both cases, one work-around is to use ROUND(A7,4) and ROUND(A8,4) if you want to allow for accuracy to fractional percentages (2 percentage decimal places), as you format them.
Another solution is to explicitly round the calculations that sourced the values in A7 and A8. You do not show us those formulas.
Neither solution is more "right" or "wrong". The choice is yours to make, depending on your intentions.
Detlef_Lewin wrote: ``A7 and A8 are 0.99999999999999989 and 0.99999999999999989``.
Not really. That is just a 17-digit approximation, which is sufficient to reproduce the binary value exactly. We can see those values in the XML file that "xslx" files are stored as, as you know.
But the exact decimal representation of the binary values is 0.999999999999999,88897769753748434595763683319091796875.
(I use period for the decimal place and comma to demarcate the first 15 significant digits.)
But the exact decimal representation of the binary values is 0.999999999999999,88897769753748434595763683319091796875.
Where did you get this value from?
Detlef_Lewin wrote, referring to 0.999999999999999,88897769753748434595763683319091796875: ``Where did you get this value from?``
64-bit binary floating-point values are the sum of 53 consecutive powers of 2, scaled by a power of 2. I wrote VBA code to calculate the exact sum. (I wrote the code 16+ years ago.)
Using a browser search, I believe you can find online "calculators" that do the same thing. But beware: some of them use the 32-bit BFP representation, which is a sum of only 24 consecutive powers of 2.