Precision problem reading from excel?

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!

[Geoff Hayes]

David - how is the column (that contains 15) formatted in excel? Are you expecting all elements of this column to be integers or can some be real (double or float data types)?

[David Pesetsky]

The range I'm asking for is just 1 cell, not a column. It's formatted as "general".

[Walter Roberson]

Can you attach an example file, using the same .xls or .xlsx ending? You might need to zip the file and attach the zip .

[David Pesetsky]

Here you go.

xlsread('15.xlsx','Sheet1','A1')

[jonas]

I unzipped the file and ran the code,

ans =

14.999999999999977

Then I opened the file -> double-clicked the cell to see if there were any hidden decimals -> found none -> clicked save anyway -> re-ran the code

ans =

    15

No idea why though. Try writing =A1-15 in another cell in excel, you will see that A1 is in fact not equal to 15. But as soon as you double-click A1, then excel rounds it to 15. Weird indeed.

[David Pesetsky]

Wow. Maybe because I got the excel from Denmark? Perhaps some strange setting within.

[jonas]

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

Answer 1

[Stephen23]

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:

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.

[Jeremy Hughes]

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]

@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:

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.

Answer 2

[jonas]

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.

[Stephen23]

"it is not an issue with xlsread."

Indeed. As my answer shows, xlsread has correctly read the actual value saved in the XML file.

The only issue I can see is that Excel pretends that 14.999999999999977 is equal to 15.

[jonas]

Thanks, that makes sense!