Using Rounded Values as Inputs to VLOOKUP causing #N/A return

Question

I have the following values in excel (A1 etc are cell references):

A1 = 0.0625 'User Input
A2 = ROUNDDOWN(A1,2) = 0.06 
A3 = ROUNDUP(A1,2) = 0.07

I then use the values of A2 and A3 in VLOOKUP calls:

B2 = IF(A2>0.3,"Out of Range",VLOOKUP(A2,data!$A$42:$B$71,2,FALSE))
B3 = IF(A3>0.3,"Out of Range",VLOOKUP(A3,data!$A$42:$B$71,2,FALSE))

B2 populates correctly. B3 returns #N/A

The range I am looking in contains the following data:

All of the data required is available, the sheet does not seem to be able to get the value for 0.07. Can anybody see why? It seems to work for all other values I have tried so far.

KEY INFO If I erase A3 = ROUNDUP(A1,2) and just type 0.07 into the cell A3, it works perfectly fine. So I'm curious about what ROUNDUP is doing, as it seemingly causes VLOOKUP to fall over.

I have tried nesting the ROUNDUP function into my VLOOKUP equation, but I get the same result.

As included in the question, if I type 0.07 as a hard value, VLOOKUP works. It seems to be directly related to ROUNDUP. A colleague asked me this, and I fixed it by giving them a routine for linear interpolation, however I am still curious as to why this is broken. — Petrichor, Jan 02 '19 at 15:26
@Kanak Tell me about it. Seems like a bug, but I'd love to know more. — Petrichor, Jan 02 '19 at 15:30
Just did a test myself and had the same problem. I found that this resolved the problem `=INT(100*ROUNDUP(A1,2))/100` which might suggest it's related to the floating point storage of decimals. But why ROUNDUP works is a mystery! — SJR, Jan 02 '19 at 15:30
I may be wrong, but Excel's low level may actually deal with binary numbers. And `0.07` is `0.00010001111010111000010100011110101110000101000111101011100001...` which implies a truncation so as to be written finitely. Excel shows you `0.07`, but it may actually have something like `0.07000001` in "mind". Hence the bug you get. `ROUND(ROUNDUP(A1, 2), 2)` does the job as well. — keepAlive, Jan 02 '19 at 15:32
@SJR Interesting, that works for me too. Seems like something that should not be happening with such an established piece of software though, doesn't it... I also figured that although the cell displays 0.07 in cell A3, it cannot be searching for that when VLOOKUP is called, so I was hoping someone here would be able to explain how/why this is. — Petrichor, Jan 02 '19 at 15:33
Read this https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/ but as I say I don't understand why ROUNDDOWN works. — SJR, Jan 02 '19 at 15:34
This link might be better https://support.microsoft.com/en-gb/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel — SJR, Jan 02 '19 at 15:37
@Kanak - what you say makes sense. I tried a few examples and only UP had problems which made me wonder if something else was going on though. — SJR, Jan 02 '19 at 15:38
See also https://stackoverflow.com/questions/29251567/vlookup-not-finding-value-in-array/29252174#29252174 — Axel Richter, Jan 02 '19 at 15:57
I have noticed that if I force Excel to 'Set Precision as Displayed' (in File>Options>Advanced) then everything works fine, so it seems this is related to the storage of the values within excel itself. However, this is far from ideal. — Petrichor, Jan 02 '19 at 16:00

Petrichor · Accepted Answer · 2019-01-02T16:53:49.567

Thanks to @Axel Richter and @SJR in the comments, it would seem that this bug is related to the manner in which Excel stores values following rounding. See the answer to this previous thread for a similar example with the Ceiling function.

There appear to be a few workarounds:

1) Using INT()

=INT(100*ROUNDUP(A1,2))/100

Rather than simply using ROUNDUP solves the issue.

2) Force Excel to Work with Displayed Precision

File > Options > Advanced > Set Precision As Displayed

However, this can lead to loss of data, and is not exactly an optimal solution

3) ROUND your ROUNDUP

ROUND(ROUNDUP(A1, 2), 2)

Thanks to @Kanak for this one.

Other Comments

In my case using =CEILING(A1,0.01) works, but in the aforementioned previous question, CEILING was actually the problem also - so this should not be considered a solution.

Applying @Axel Richter's answer from before to my question shows that using ROUNDUP can lead to a small difference between what is displayed in the cell and the value that Excel is actually storing. In my case, the error in rounding is as follows:

 Sub testRoundup()

 Dim v As Double, test As Boolean, diff As Double

 v = [ROUNDUP(0.0625,2)]            '0.07
 test = (v = 0.07)                  'FALSE
 diff = 0.07 - v                    '1.38777878078145E-17

End Sub

Another good resource.

Axel Richter · Answer 2 · 2019-01-02T18:46:30.377

As an addition to the given answer:

The IEEE specification of floating point arithmetic is not to blame for this problem. Instead it is clearly a bug in implementation of CEILING and ROUNDUP. Rounding should be the solution while using floating point numbers and should not be the problem.

Thanks to the fact that *.xlsx files are simply ZIP archives, we can simply unzip it and have a look into what is really stored. As an example in /xl/worksheets/sheet1.xml we find:

<sheetData>
 <row r="1" spans="1:2">
  <c r="A1">
   <f>ROUNDUP(0.0625,2)</f>
   <v>6.9999999999999993E-2</v>
  </c>
  <c r="B1">
   <f>ROUND(ROUNDUP(0.0625,2),2)</f>
   <v>7.0000000000000007E-2</v>
  </c>
 </row>
 <row r="2" spans="1:2">
  <c r="A2">
   <f>CEILING(0.0625,0.01)</f>
   <v>7.0000000000000007E-2</v>
  </c><c r="B2">
   <f>ROUND(CEILING(0.0625,0.01),2)</f>
   <v>7.0000000000000007E-2</v>
  </c>
 </row>
 <row r="3" spans="1:2">
  <c r="A3">
   <f>ROUNDUP(15.25,1)</f>
   <v>15.299999999999999</v>
  </c>
  <c r="B3">
   <f>ROUND(ROUNDUP(15.25,1),1)</f>
   <v>15.3</v>
  </c>
 </row>
 <row r="4" spans="1:2">
  <c r="A4">
   <f>CEILING(15.1,0.1)</f>
   <v>15.100000000000001</v>
  </c>
  <c r="B4">
   <f>ROUND(CEILING(15.1,0.1),1)</f>
   <v>15.1</v>
  </c>
 </row>
</sheetData>

As we see here, the claiming: "storing only 15 significant digits of precision" is not really true. Instead the real numbers in IEEE 754 double precision having up to 17 digits of precision are stored. And additionally ROUNDUP and CEILING sometimes are not storing the rounded values. And this is a bug in my opinion as I said. ROUND and ROUNDDOWN are always storing the rounded values as it should.

Using Rounded Values as Inputs to VLOOKUP causing #N/A return

2 Answers2