Numerical difference between Matlab result and MS Excel result

Question

Zhi Hao 2011-6-27

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/10327-numerical-difference-between-matlab-result-and-ms-excel-result

Hi,

I am interested in obtaining exact output between matlab calculation and MS excel computation.

Here's an example. In excel, key in this: 123456789123456789

Result in MS excel: 123456789123456000

In matlab, key in this: 123456789123456789

Result in Matlab: 123456789123457000

Can anyone advise me how can I eliminate the difference between the two platforms?

zhi hao

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Doug Eastman 2011-6-27

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/10327-numerical-difference-between-matlab-result-and-ms-excel-result#answer_14217

在 MATLAB Online 中打开

When I type '123456789123456789' in a numeric field in Excel, I get 123456789123456000. The issue is due to floating point data types, discussed extensively (for example see Cleve's Corner. If you type

eps(123456789123456789)

You can see the accuracy around that number for a double data type is 16, so in MATLAB the value represented is 123456789123456780.

The only way I see to use the full number is Excel is to format the cell as text.

In MATLAB there are a couple of options, you could use VPA in the Symbolic Math Toolbox, or you could use the 64-bit integer data type, try:

uint64(123456789123456789)

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Walter Roberson 2011-6-27

Note: until quite recent releases, uint64(123456789123456789) would construct the double precision number corresponding to 123456789123456789 and then would convert that number to uint64. There was no direct way in those releases to get a uint64 number that needed more than 53 bits.

请先登录，再进行评论。