17 views (last 30 days)

Hi everyone,

I am new to Matlab coding, and would be great if some one could help me out to solve my issue.

I have an excel sheet with multiple data columns; as an example, I have first set of data as 3 columns of 1, the second set of data as 3 columns of 2, and the third set of data as 3 columns of 3. The number of rows in each data set is not equal.

1 1 1 2 2 2 3 3 3

1 1 1 2 2 2 3 3 3

1 1 1 2 2 2 3 3 3

1 1 1 3 3 3

3 3 3

I would like to multiply the first column in data set1 with 2, then multiply second column of data set1 by 3, and the third coumn of data set1 by 4. Then I would like to repeat this for dataset2 and dataset3.

The expected output;

2 3 4 4 6 8 6 9 12

2 3 4 4 6 8 6 9 12

2 3 4 4 6 8 6 9 12

2 3 4 6 9 12

6 9 12

Then I would like to save this as a cell array;

where the first 3 columns (dataset1) is to be saved as cell1, and second 3 sets of columns to be saved as cell2, and last 3 columns to be saved as cell3.

in this format;

tracks = cell(3, 1);

Would be great if someone could help me with this! I am so new to excel imports and cell arrays...

Thanks a lot!

Sara

jonas
on 23 May 2018

Edited: jonas
on 23 May 2018

The following solution is inefficient but works for R2016b and later. See Guillaume's answer for better solutions, also including one for earlier release

data=xlsread('filename.xlsx');

data2 = nan(size(data));

for i=1:size(data,2)/3

data2(:,(i*3)-2:i*3)=data(:,(i*3)-2:i*3).*[2 3 4];

tracks{i}=data2(:,(i*3)-2:i*3)

end

Andrei Bobrov
on 23 May 2018

>> d

d =

1 1 1 2 2 2 3 3 3

1 1 1 2 2 2 3 3 3

1 1 1 2 2 2 3 3 3

1 1 1 0 0 0 3 3 3

0 0 0 0 0 0 3 3 3

out = d .* repmat(2:4,1,3);

C = mat2cell(out,size(out,1),[3 3 3]);

Guillaume
on 23 May 2018

Another option that would avoid the repmat is to temporarily reshape the matrix as a 3D array, perform the multiplication with the vector [2 3 4], and split that 3D array by page. As a one-liner:

%R2016b or later:

out = squeeze(num2cell(reshape(data, size(data, 1), 3, []) .* [2 3 4], [1 2]));

%pre R2016b:

out = squeeze(num2cell(bsxfun(@times, reshape(data, size(data, 1), 3, []), [2 3 4])));

In theory this should be faster as reshape is a very fast operation.

Guillaume
on 24 May 2018

To sum up, including the removal of the nans, this is all that is needed:

tracks = squeeze(num2cell(bsxfun(@times, reshape(data, size(data, 1), 3, []), [2 3 4])));

tracks = cellfun(@(m) m(all(isfinite(m), 2), :), tracks, 'UniformOutput', false);

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

Start Hunting!
## 1 Comment

## Direct link to this comment

https://www.mathworks.com/matlabcentral/answers/402094-import-excel-sheet-multiply-each-column-with-a-variable-and-save-the-results-as-a-new-cell-array#comment_570967

⋮## Direct link to this comment

https://www.mathworks.com/matlabcentral/answers/402094-import-excel-sheet-multiply-each-column-with-a-variable-and-save-the-results-as-a-new-cell-array#comment_570967

Sign in to comment.