1
$\begingroup$

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}]
$\endgroup$
  • 3
    $\begingroup$ This feels like the result of the inevitably imperfect digital representation of floating point numbers. Can you represent your data in a different format? $\endgroup$ – Michael Stern Jun 7 '16 at 22:36
  • $\begingroup$ @MichaelStem i tried to format the cells in excel to text, general and number. Nothing works. Still the numbers seems different in mathematica. $\endgroup$ – dee Jun 7 '16 at 22:56
  • 1
    $\begingroup$ Dee, 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. $\endgroup$ – MarcoB Jun 7 '16 at 23:53
  • 1
    $\begingroup$ @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. $\endgroup$ – dee Jun 8 '16 at 0:33
  • 1
    $\begingroup$ If 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. $\endgroup$ – george2079 Jun 8 '16 at 15:40

Browse other questions tagged or ask your own question.