How can I take the average of certain columns in each excel sheets

I have an excel file with multiple sheets. In sheet # 1, I want to take the average of column 1 and the average of column 5, then store each result in a new file. Iwant to repeat this process to all the remaining sheets in my excel file.

[Dyuman Joshi]

Read the excel sheet using readmatrix or readtable, use indexing to take the mean of the columns 1 and 5, and use writematrix or writetable to store the result in a new file.

If you have any more questions, show what you have attempted and ask a specific question (where you are having trouble).

Answer 1

[Mathieu NOE]

hello Marion

see my example below (the dummy excel file is attached)

hope it helps

% Importing Data from excel across multiple sheets.
filename = 'Classeur1.xlsx';
[~,sheet_name]=xlsfinfo(filename);
nsheets = numel(sheet_name);

% retrieve and process data
for k=1:nsheets 
  T = readmatrix(filename,"Sheet",sheet_name{k}); %  readtable or readmatrix, readcell
  col1_averaged(k,:) = mean(T(:,1)); % average of column 1
  col5_averaged(k,:) = mean(T(:,5)); % average of column 5
end

%% export results as table
out_table = array2table([col1_averaged col5_averaged],'VariableNames',{'col 1 averaged' 'col 5 averaged'});
writetable(out_table,'out.xlsx',"Sheet",1);