keeping precision when writing to excel
6 次查看(过去 30 天)
显示 更早的评论
Hi
I have the following script
load ASqr.txt
load BSqr.txt
load C.txt
load delta.txt
realZeros = (1/pi).*(((delta./ASqr).*(0.9999999))-((delta./ASqr).*(0)));
format long g
realZeros
That I would like to output the answer to excel. However, when I use
save realZeros.xls realZeros -ascii -double
Precision is still being lost? Would anyone know where Im going wrong?
Thanks
0 个评论
回答(1 个)
dpb
2014-3-10
编辑:dpb
2014-3-10
Per the doc for save,
FORMAT: Specify the format of the file, regardless of any specified
extension. Use one of the following combinations:
...
'-ascii' 8-digit ASCII format.
...
'-ascii', '-double' 16-digit ASCII format.
You'll get 16 digits in the file. Now how much precision there really is in those values will depend entirely on what the content of the loaded data files is. I note that if you change your computation slightly by setting the "0" in the last term you may well begin to introduce loss of precision by cancellation.
Also I'll reiterate that the
format long g
has no bearing whatever on anything except the command window display. And again, I'll use the equivalent of the demo I used when we were discussing xlswrite and you had same question--
>> p=pi;
>> whos p
Name Size Bytes Class Attributes
p 1x1 8 double
>> save 'test.txt' p -ascii -double
>> type 'test.txt'
3.1415926535897931e+00
>> p1=textread('test.txt')
p1 =
3.1416
>> p1==p
ans =
1
>>
In general one should get full precision back as demonstrated.
So, again, show IN DETAIL what makes you think you're getting a loss of precision. If it's again that you're looking at displayed cell content in Excel to determine this, we're right back to the place we were in that previous thread.
2 个评论
dpb
2014-3-10
编辑:dpb
2014-3-10
Whatever it is that you think you want or should get, it is NOT a Matlab problem of loss of precision.
OK, if I load the above test file into Excel and look at that first cell, it appears that Excel, indeed, doesn't store full precision (why am I not particularly surprised?).
That, unfortunately, is Excel's problem, NOT Matlab's and Matlab can't do anything to fix Gates' minions' boo-boos. You can verify it is Excel if you use my simple test file because it's easy to see the correct image of the text file in the import lizard window but that the actual stored value ends up as
3.14159265358979
Sorry, it appears that's "just the way it is" and another demonstration of limitations of Excel viz a viz a "real" numerical program such as Matlab. So, the lesson to be learned looks to be "use Matlab instead".
Now, what do you want to do in Excel you can't do as (or more) easily in Matlab?
BTW, I guess since you never showed us, you found the same precision limit with xlswrite? I suppose given the symptom above it would have to be...oh, I can check that again real quick from the previous test--I never did actually look that much...well, indeed it is.
Anyway, the problem is Excel's not Matlab's and there's nothing to be done because even if I copy the full precision text from the Matlab command window into another cell in Excel it immediately truncates it to the same precision.
Talk to Microsoft.
ADDENDUM:
Got to wondering whether this is real or just a fignewton of the display formattting not showing full precision. Loaded the text file version containing the full-precision string then saved as a .xls file of new name from Excel. Then read into Matlab and compare--
>> p2=xlsread('excel.xls')
p2 =
3.1416
>> p2==pi
ans =
0
>> p2-pi
ans =
-3.1086e-15
>>
So, indeed, it is real. I'd kinda' forgotten about it but seems like I recall talk that Excel may use one of the offbeat x87 FP processor modes rather than full 80-bit or IEEE...something surely is less than one would hope for, indeed.
So, your conclusion of losing precision in Excel is true but Matlab has NOTHING WHATSOEVER TO DO WITH IT. If you really need those last couple of digits, then don't (and in fact you can't) use Excel.
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!