keeping precision when writing to excel

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

Answer 1

[dpb]

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.

[Amy]

for example, the data from ASqr.txt that was loaded in was calculated by running the following script:

    %aSqrSum.m
    %
    %
    %Amy Wallace, 06/03/2014

    %Variable dictionary
    %Nin Number of terms, in a vector, to sum
    %N number to sum
    %ASqr Sum of geometric series
    %x vector of constants
    %n Loop counter

    Nin = inputdlg('Enter space-seperated numbers:', 'N vector', [1 50]);
    N = str2num(Nin{:});

    x = [0.9 0.99 0.999 0.9999 0.99999 0.999999 0.9999999];
    ASqr = zeros(length(N), length(x));
    for ii = 1:length(N)
    for n = 0:N(ii)-1
       ASqr(ii,:) = ASqr(ii,:) + (x.^(2*n));
    end
    end

   format long e %Sets display format to 15 digits 
   ASqr

When I then run the realZeros script in the matlab command window it is producing numbers up to 16 decimal places, precision has been kept when saving to a txt file but when I save to an excel file, as I would like to display the data in tables, when I go up to 16decimal places it starts to replace the last few numbers with zero?

[dpb]

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.