Clear Filters
Clear Filters

Write table in Excel with percentages and decimals.

28 views (last 30 days)
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:
excel table 2.jpg
... 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.

Answers (1)

Rohan Patidar
Rohan Patidar on 22 Mar 2019
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?

Tags

Products


Release

R2018b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!