Write table in Excel with percentages and decimals.

Good morning everyone,

I'm writing my first "question" in "Matlab Answers".

I'm using Matlab and the problem is the following:

I have few tables that I need to write on a spreadsheet in Excel, but they are supposed to have different kind of datas: some of them are supposed to be "transformed in percentages", while others just need to be cut of unnecessary decimals (say setting a precision of 4 decimals after the comma).

This is the data I have to print on a table:

      0.0022835         -0.0034148       -0.00056562  
      -0.033729          -0.023028         -0.010711  
       0.017093           0.015746          0.011102  
       0.071375           -0.10197         -0.017716  
        0.07079           -0.10586         -0.017534  
       0.010817          0.0099986         0.0047263  
         1.1754            -1.9015          -0.66633  
         1.3995            -2.6692          -0.93321  

I need to transform the first six rows into percentages and just print the other ones, e.g. needs to be displayed as , and so on. I know that in Excel it is sufficient to select a cell and convert it into a percentage: Excel multiplies the number inside the cell and attach it to the percentage symbol. However I don't know how to make Matlab tell Excel to do it automatically, e.g. with a command like this:

    writetable(myTable, filePath, 'Format', 'Percentage')  % --> I'm just guessing how would a command like this look like  

So I worked it around and transformed it in a cell array and worked on the single rows:

    % (1) Translate cells in strings, stick the percentage symbol to them
    statsCell_tmp(1:6,:) = cellfun(@(x) num2str(100*x, '% .2f%%'), statsCell_tmp(1:6, :), 'UniformOutput', 0);
    
    % (2) expose just few decimals
    statsCell_tmp(7, :)  = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(7, :), 'UniformOutput', 0);
    statsCell_tmp(8, :)  = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(8, :), 'UniformOutput', 0);

and the result that I get in my workspace is this, after using cell2table(statsCell_tmp):

    

    '0.23%'     '-0.34%'     '-0.06%'  
    '-3.37%'    '-2.30%'     '-1.07%'  
    '1.71%'     '1.57%'      '1.11%'   
    '7.14%'     '-10.20%'    '-1.77%'  
    '7.08%'     '-10.59%'    '-1.75%'  
    '1.08%'     '1.00%'      '0.47%'   
    '1.1754'    '-1.9015'    '-0.6663' 
    '1.3995'    '-2.6692'    '-0.9332' 

However, when I use writetable I get a very disappointing result on Excel:

As you can see, the last two lines have the exact same digits of their Matlab-display version, but the "." has been moved.

In a nutshell, "writetable" is not preserving the shape of the datas inside the table. Also, if I avoid to transform the last two rows in string, they're printed with the correct decimals:

... however, I am required to use the same notations for decimals (and to do it automatically), so even this solution is not working; besides, it has already been pointed outit has already been pointed out that it's not possible to use the comma as a decimal separator in Matlab.

In a nutshell: how am I supposed to come up with a unique (and correct) notation for these tables?

Thank you for your time and patience.

Answer 1

[Rohan Patidar]

Hi Eri,

I understand you are getting discrepancy when you load matlab saved file into excel. Specially the last 2 rows where the position of decimal is not as expected.

I tried running the above code with your data:

statsCell_tmp = {
    0.0022835,  -0.0034148,   -0.00056562;
    -0.033729,  -0.023028,     -0.010711 ;
    0.017093,   0.015746,     0.011102  ;
    0.071375,   -0.10197,     -0.017716  ;
    0.07079,    -0.10586,      -0.017534  ;
    0.010817,   0.0099986,       0.0047263  ;
    1.1754,     -1.9015,         -0.66633  ;
    1.3995,     -2.6692,     -0.93321 };

statsCell_tmp(1:6,:) = cellfun(@(x) num2str(100*x, '% .2f%%'), statsCell_tmp(1:6, :), 'UniformOutput', 0);
statsCell_tmp(7, :)  = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(7, :), 'UniformOutput', 0);
statsCell_tmp(8, :)  = cellfun(@(x) num2str(x, '%.4f'), statsCell_tmp(8, :), 'UniformOutput', 0);
% This saves data to Test.csv
writetable(cell2table(statsCell_tmp), 'Test.csv');

On opening the Test.csv file, I am able to see the expected result that is

1.1754

1.3995 ..

Is this what you were looking for?