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

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);

[Tiago Dias]

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]

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.

[Tiago Dias]

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?