I get a different mean in matlab that i got from Excel for the same input

Question

Tiago Dias 2018-3-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/385719-i-get-a-different-mean-in-matlab-that-i-got-from-excel-for-the-same-input

评论： Tiago Dias 2018-3-1

A.mat

Hello,

So my input table is A.mat, since I have NaN values in there I use nanmean for the calculation. using nanmean from Matlab or average with excel I get the same value (as expected) 52.8926514537196000. now I wanna do the calculation for example of A(3,1) - nanmean(A). (because the first are equal, as I made a logical value in excel)

From Matlab I get 7.22209082533311000, from excel I get 7.22209082533308000, why the two values are different? I made a verification in excel, the two means are the same, the only "problem" may be related to the operation "-", any ideas why

Thanks for your time.

load('A.mat');
media_mc = mean(A);
media_nan_mc = nanmean(A);
calculation = A(3,1) - nanmean(A);

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

Tiago Dias 2018-3-1

For the rest os the values, the first 12 decimal numbers are equal, the problem is with the 13 and 14 decimals number...maybe some limitation on the excel side?

请先登录，再进行评论。

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

Answer 1

Guillaume 2018-3-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/385719-i-get-a-different-mean-in-matlab-that-i-got-from-excel-for-the-same-input#answer_307885

在 MATLAB Online 中打开

If matlab and excel wrote a 1000 digits after the decimal point and the 999th digit differed would you still worry about the difference?

Looking at difference in the 12th digit of precision is pointless. A slightly difference in algorithm can easily result in that difference. For all intent and purpose, the two numbers are exactly the same.

You probably need to learn more about floating point numbers and their accuracy. Your next question might be why is 0.1 + 0.1 + 0.1 not equal to 0.3?

>> isequal(0.1+0.1+0.1, 0.3)
ans =
  logical
   0

Note that both excel and matlab are lying to you anyway. For example, they cannot store the number 0.1 exactly, they're storing its nearest representation in binary that they round to 0.1 when displaying the number.

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

Tiago Dias 2018-3-1

image.png

Thanks for you answer, now i got one question.

The average of my data is 52.89265145371962 in matlab, when i copy to excel is only shows 52.8926514537196. the number "2" is ignored, any idea how to fix this?

请先登录，再进行评论。