xlsread problem regarding reading values

While i read a excel sheet using xlsread, it doesn't show whole value in the variable file but when i print the value i shows more decimal places than the actual value.

For eg. the actual number in excel sheet is 3.53719759

the value shown in variable when i open it 3.5372

the value shown when i print it is 3.53719758987427

How can i get the exact same value as it is in the excel sheet?

[Dyuman Joshi]

"the value shown in variable when i open it 3.5372"

The value is shown as such i.e. 4 digits after decimal, as it is the default output display format, 'short', for numeric datatype in MATLAB.

"the value shown when i print it is 3.53719758987427"

How exactly do you print it? This is not the 'long' format as it has 14 digits after decimal. Or did you missed a digit in copy pasting?

"but when i print the value i shows more decimal places than the actual value."

You have it the other way around. The floating point number is stored with (approximately) 16 digit precision but the default format (as mentioned above) displays only 4 digits after the decimal.

"How can i get the exact same value as it is in the excel sheet?"

If you want to obtain that in numeric format, it is not possible. You can, however, obtain that in text format -

format long
x = pi

x = 
   3.141592653589793

sprintf('%0.8f', x)

ans = '3.14159265'

sprintf("%0.8f", x)

ans = "3.14159265"

You can round a number upto 8 digits after decimal, but the output will not exactly be same as what you want.

round(x,8)

ans = 
   3.141592650000000

[Stephen23]

"How exactly do you print it?": try FORMAT LONG G

[Atharva Bhomle]

i used disp() to print it

[Stephen23]

"How can i get the exact same value as it is in the excel sheet?"

MS Excel uses double binary floating point numbers:

https://learn.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

MATLAB uses double binary floating point numbers (by default):

https://www.mathworks.com/help/matlab/matlab_prog/floating-point-numbers.html

MATLAB imports the spreadsheet values so that it retrieves exactly the save double value that MS Excel saves to file (which is no different to how MS Excel also imports saved data). So you will get exactly the same value in memory.

Most likely you are confusing how values are displayed with the actual values stored in memory. Not the same things at all. But because you did not explain what you are trying to achieve, we cannot guess how to help you.

[Walter Roberson]

Note though that .xlsx files are zip'd directories of mostly XML that stores numbers as text . It is hypothetically possible that when MATLAB converts the text number back to binary, that it might not be bit-for-bit identical to the original number.

If I recall correctly, there are some numbers that need a 17th decimal digit to resolve to the last bit, and it would not astonish me if routinely only 16 decimal digits were stored into the .xlsx file. Maybe even onl 15 decimal digits (by default MATLAB displays only 15 decimal digits, so it is common for MATLAB's display of numbers to not display enough decimal digits to resolve the last bit.)

[Stephen23]

"and it would not astonish me if routinely only 16 decimal digits were stored into the .xlsx file"

Apparently XLSX uses up to 17 digits for storing double values:

https://techcommunity.microsoft.com/t5/excel/numbers-stored-in-xml-file/m-p/3767502

[Walter Roberson]

By the way, https://stackoverflow.com/questions/68784030/how-do-we-need-17-significant-decimal-digits-to-identify-an-arbitrary-double-pre gives an example of a number that requires 17 digits, 50388143.0682372152805328369140625

I just tested with writematrix() and confirmed that MATLAB for one writes out enough digits to be able to exactly replicate the number: writetable() writes out 50388143.068237215

Unfortunately at the moment I do not have genuine Excel handy to test what Excel does.

Answer 1

[Divyanshu]

The values are getting rounded off because by default MATLAB uses ‘short’ format and hence displays numbers only up to 4 decimal places.

In order to get the correct values, follow these steps:

• Firstly, set the format to ‘long’, you can do these by executing command ‘format long’.
• And then read the value from excel file using ‘readtable’ function.

Please refer the following documentation for further details:

Set output display format - MATLAB format (mathworks.com)