Precision problem reading from excel?
12 次查看(过去 30 天)
显示 更早的评论
I have an excel book. In the cell is exactly 15. I use: num=xlsread(envelopeFile,envelopeSheet,excelRange) ...and it's reporting 14.999999999999977. So then all my if-then's etc. are lying to me. Do I need to start rounding off?
Arg!
7 个评论
jonas
2018-5-29
Still, this is a little bit weird (see attached fig).
A1 was obtained from xlswrite
xlswrite('test.xlsx',14.999999999999977)
and B1 was added directly in excel
回答(2 个)
Stephen23
2018-5-29
编辑:Stephen23
2018-5-29
I suspect that Excel (in the typical way of Microsoft) is doing some magic to prevent people from trying to enter numbers that have digits close to the limit supported by its floating point numbers. It seems the two 7's are beyond what can be entered by hand inside Excel (I can only enter fourteen 9's by hand, it refuses any more), but are able to be written using xlswrite (or whatever you use to create that file). Once the value is saved inside the file then editing the cell might force Excel to re-evaluate that number and it rounds it up to what it considers to be an "acceptable" value.
You could ask about this on an Excel forum.
"In the cell is exactly 15"
Maybe. However the value in the file is clearly NOT 15. We can check this by looking at the XML itself:
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/188852/image.png)
This means xlsread has read the value correctly. The only problem is that Excel pretends that such a value does not exist, possibly in an attempt to shield its users from the realities of floating point numbers. And without writing to the file there is no guarantee that the file contains the same value as the cell in Excel memory shows.
Solution: force the cell to be evaluated by Excel (e.g. select the cell and press enter), then save the file. You will then find that the value in the file matches what Excel thinks it is.
Rounding inside MATLAB should be avoided, if you need to guarantee the same values as Excel used.
2 个评论
Jeremy Hughes
2018-5-29
Thanks Stephen,
I've seen this a number of times, and I'm happy to see such a good explanation.
It's odd that Excel stores numbers as text. What I also find stranger, is that when Excel loads this number, and since it's indistinguishable from "15", that they don't just store "15".
Bottom line, XLSX isn't the best data storage format for floating point numbers when precision matters.
Jeremy
Stephen23
2018-5-29
编辑:Stephen23
2018-5-29
@Jeremy Hughes: thank you for your feedback.
"It's odd that Excel stores numbers as text"
Of course with a pure XML doc there is not much choice :(
"... when Excel loads this number, and since it's indistinguishable from "15", that they don't just store "15"."
Because loading and storing are two different operations. It appears that Excel loads the value and somehow converts it internally to 15, but this is not stored in the file unless that cell is edited and the file saved. I just tried opening the file, selecting that cell, pressing enter, and then saving the file. This was the result:
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/189093/image.png)
So until the cell is written, there is no guarantee that the file contains the same value as the worksheet shows. Which makes sense, really, just is a little unexpected for users who think that those two values should be the same.
"XLSX isn't the best data storage format for floating point numbers when precision matters."
Yes, text just can't store the precision (well, not without James Tursa's num2strexact and an endless number of digits, or by storing a hex/binary string (why don't they?)). But I think the benefits of the XML office documents outweigh their disadvantages: they are certainly much more stable than the old binary formats.
jonas
2018-5-29
编辑:jonas
2018-5-29
You can actually reproduce this issue by typing:
xlswrite('test15.xlsx',14.999999999999977)
The cell in excel will say 15, but will actually store the correct number until you edit the cell, after which it automatically updates and rounds to 15.
So, probably the error stems from how the excel sheet was created in the first place and it is not an issue with xlsread.
2 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!