decimal point changes in mathematica after importing data from excel

1

I am trying to import data from excel into mathematica. It works fine except that the values of all the variables after the import into mathematica change at 6th decimal point. For example, variable QEF is 15.51110000000 in excel but in mathematic after the import it becomes 15.5110999999. Similarly all other variables are also changed. These are small changes but after solving the model there is discrepancy between initial values and the solution. Anyone aware of this issue? Thanks.

excel file

I used following code:

dir = SetDirectory[NotebookDirectory[]];
file = "11.xls";

tf = TableForm;

SetDirectory[dir];
dat = Flatten[Sort[ToExpression[Import[file]]], 1]; dat // tf;

dat0 = Import[file, {"Data", 1}]; dat0 // tf;

dat = Table[{ToExpression[dat0[[i, 1]]], dat0[[i, 2]]}, {i,Length[dat0]}];

vl = 58; pl = 29; ex = 17;
vareval = Table[dat[[i, 1]] ->  dat[[i, 2]], {i, vl}]
pareval = Table[dat[[i, 1]] -> dat[[i, 2]], {i, vl + 1, vl + pl}]
exog = Table[dat[[i, 1]] -> dat[[i, 2]], {i, vl + pl + 1, vl + pl + ex}]

dee

Posted 2016-06-07T20:15:35.430

Reputation: 21

Question was closed 2018-05-30T00:15:45.270

3This feels like the result of the inevitably imperfect digital representation of floating point numbers. Can you represent your data in a different format? – Michael Stern – 2016-06-07T22:36:41.377

@MichaelStem i tried to format the cells in excel to text, general and number. Nothing works. Still the numbers seems different in mathematica. – dee – 2016-06-07T22:56:51.780

I can't seem to reproduce your problem by creating my own Excel file and adding that value in a cell. Can you share an Excel file that displays the problem, and the exact code you used in MMA to import it? – MarcoB – 2016-06-07T23:23:30.810

@MarcoB attached excel file. I am using following code for importing the data. dir = SetDirectory[NotebookDirectory[]]; file = "11.xls"; tf = TableForm; SetDirectory[dir]; dat = Flatten[Sort[ToExpression[Import[file]]], 1]; dat // tf; dat0 = Import[file, {"Data", 1}]; dat0 // tf; dat = Table[{ToExpression[dat0[[i, 1]]], dat0[[i, 2]]}, {i, Length[dat0]}]; vl = 58; pl = 29; ex = 17; vareval = Table[dat[[i, 1]] -> dat[[i, 2]], {i, vl}] pareval = Table[dat[[i, 1]] -> dat[[i, 2]], {i, vl + 1, vl + pl}] exog = Table[ dat[[i, 1]] -> dat[[i, 2]], {i, vl + pl + 1, vl + pl + ex}] – dee – 2016-06-07T23:34:04.990

1Dee, please add the code, properly formatted, to your original post, as well as the link to the excel file. You can edit the original post by clicking on the "edit" link right under it. – MarcoB – 2016-06-07T23:53:53.250

@MarcoB I have edited the original post. Thanks. I am new to this forum. Still learning. – dee – 2016-06-08T00:17:29.683

@dee Thank you for adding the code and the file. I'm afraid, however, that I still can't reproduce the behavior your observe on my system. Here is a screenshot of what I see when I run your code: results. As you can see, QEF is imported as $15.5111$ as one would expect. I am afraid that this might be version / OS dependent. I am using MMA 10.4.0 on Win7-64; what about you?

– MarcoB – 2016-06-08T00:26:32.777

1@MarcoB I am using MMA 8.0. I get the same results as yours. If you try to convert the cell to inputform, you will see that after fifth decimal point, MMA adds numbers to the variable, which were originally not there in excel. It is this difference that causes the solution to vary from initial values. – dee – 2016-06-08T00:33:52.413

@dee it no longer does that in version 10.4: inputform. That's why I'm saying that it is likely to be a version-dependent problem.

– MarcoB – 2016-06-08T00:42:48.070

@MarcoB Thanks a lot. I will try to find later version. – dee – 2016-06-08T00:45:50.290

1If you are stuck with the old mathematica version, you can use the Excel text() function to convert the numbers to a desired format, then run through ToExpression when you import. – george2079 – 2016-06-08T15:40:32.190

No answers