How can I take the average of certain columns in each excel sheets
3 views (last 30 days)
Show older comments
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.
1 Comment
Dyuman Joshi
on 20 Jul 2023
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).
Answers (1)
Mathieu NOE
on 21 Jul 2023
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);
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!