Mixing
Using Rounded Values as Inputs to VLOOKUP causing #N/A return
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.
excel vlookup
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
|
show 6 more comments
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.
excel vlookup
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
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 2 at 15:26
3
Just did a test myself and had the same problem. I found that this resolved the problem=INT(100*ROUNDUP(A1,2))/100which might suggest it's related to the floating point storage of decimals. But why ROUNDUP works is a mystery!
– SJR
Jan 2 at 15:30
1
I may be wrong, but Excel's low level may actually deal with binary numbers. And0.07is0.00010001111010111000010100011110101110000101000111101011100001...which implies a truncation so as to be written finitely. Excel shows you0.07, but it may actually have something like0.07000001in "mind". Hence the bug you get.ROUND(ROUNDUP(A1, 2), 2)does the job as well.
– keepAlive
Jan 2 at 15:32
2
Read this microsoft.com/en-us/microsoft-365/blog/2008/04/10/… but as I say I don't understand why ROUNDDOWN works.
– SJR
Jan 2 at 15:34
2
See also stackoverflow.com/questions/29251567/…
– Axel Richter
Jan 2 at 15:57
|
show 6 more comments
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.
excel vlookup
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
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.
excel vlookup
excel vlookup
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
asked Jan 2 at 15:09
PetrichorPetrichor
4521316
4521316
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 2 at 15:26
3
Just did a test myself and had the same problem. I found that this resolved the problem=INT(100*ROUNDUP(A1,2))/100which might suggest it's related to the floating point storage of decimals. But why ROUNDUP works is a mystery!
– SJR
Jan 2 at 15:30
1
I may be wrong, but Excel's low level may actually deal with binary numbers. And0.07is0.00010001111010111000010100011110101110000101000111101011100001...which implies a truncation so as to be written finitely. Excel shows you0.07, but it may actually have something like0.07000001in "mind". Hence the bug you get.ROUND(ROUNDUP(A1, 2), 2)does the job as well.
– keepAlive
Jan 2 at 15:32
2
Read this microsoft.com/en-us/microsoft-365/blog/2008/04/10/… but as I say I don't understand why ROUNDDOWN works.
– SJR
Jan 2 at 15:34
2
See also stackoverflow.com/questions/29251567/…
– Axel Richter
Jan 2 at 15:57
|
show 6 more comments
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 2 at 15:26
3
Just did a test myself and had the same problem. I found that this resolved the problem=INT(100*ROUNDUP(A1,2))/100which might suggest it's related to the floating point storage of decimals. But why ROUNDUP works is a mystery!
– SJR
Jan 2 at 15:30
1
I may be wrong, but Excel's low level may actually deal with binary numbers. And0.07is0.00010001111010111000010100011110101110000101000111101011100001...which implies a truncation so as to be written finitely. Excel shows you0.07, but it may actually have something like0.07000001in "mind". Hence the bug you get.ROUND(ROUNDUP(A1, 2), 2)does the job as well.
– keepAlive
Jan 2 at 15:32
2
Read this microsoft.com/en-us/microsoft-365/blog/2008/04/10/… but as I say I don't understand why ROUNDDOWN works.
– SJR
Jan 2 at 15:34
2
See also stackoverflow.com/questions/29251567/…
– Axel Richter
Jan 2 at 15:57
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 2 at 15:26
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 2 at 15:26
3
3
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 2 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 2 at 15:30
1
1
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 2 at 15:32
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 2 at 15:32
2
2
Read this microsoft.com/en-us/microsoft-365/blog/2008/04/10/… but as I say I don't understand why ROUNDDOWN works.
– SJR
Jan 2 at 15:34
Read this microsoft.com/en-us/microsoft-365/blog/2008/04/10/… but as I say I don't understand why ROUNDDOWN works.
– SJR
Jan 2 at 15:34
2
2
See also stackoverflow.com/questions/29251567/…
– Axel Richter
Jan 2 at 15:57
See also stackoverflow.com/questions/29251567/…
– Axel Richter
Jan 2 at 15:57
|
show 6 more comments
2 Answers
2
active
oldest
votes
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.
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
add a comment |
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.
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008705%2fusing-rounded-values-as-inputs-to-vlookup-causing-n-a-return%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
add a comment |
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.
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
add a comment |
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.
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
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.
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
edited Jan 2 at 16:53
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
answered Jan 2 at 16:48
PetrichorPetrichor
4521316
4521316
add a comment |
add a comment |
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.
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
add a comment |
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.
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
add a comment |
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.
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
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.
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
edited Jan 2 at 18:46
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
answered Jan 2 at 18:34
Axel RichterAxel Richter
26.4k32039
26.4k32039
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54008705%2fusing-rounded-values-as-inputs-to-vlookup-causing-n-a-return%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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 2 at 15:26
3
Just did a test myself and had the same problem. I found that this resolved the problem
=INT(100*ROUNDUP(A1,2))/100which might suggest it's related to the floating point storage of decimals. But why ROUNDUP works is a mystery!– SJR
Jan 2 at 15:30
1
I may be wrong, but Excel's low level may actually deal with binary numbers. And
0.07is0.00010001111010111000010100011110101110000101000111101011100001...which implies a truncation so as to be written finitely. Excel shows you0.07, but it may actually have something like0.07000001in "mind". Hence the bug you get.ROUND(ROUNDUP(A1, 2), 2)does the job as well.– keepAlive
Jan 2 at 15:32
2
Read this microsoft.com/en-us/microsoft-365/blog/2008/04/10/… but as I say I don't understand why ROUNDDOWN works.
– SJR
Jan 2 at 15:34
2
See also stackoverflow.com/questions/29251567/…
– Axel Richter
Jan 2 at 15:57