Average of data multiples excel sheets

I have one excel file that contains 40 sheets with two column dataseries in each of the sheets. I need a matlab codes that can average the data for the sheet1 - sheet 10, sheets 11 - sheet 20, sheet 21-sheet30, sheet31-sheet40 separately and write the results in sheet 41, sheet 42, sheet 43 and sheet 44 respetively. Can anyone help. Thanks.

Olusola

[Mathieu NOE]

hi

can you share a representative excel file ?

tx

[Ojo Olusola]

a = xlsread('mydata2a.xlsx')

Answer 1

[Mathieu NOE]

hello

so this is my suggestion

as we have here 10 sheets , my demo works for either groupped 2 or 5 sheets

this is driven by the parameter k , and of course it must be choosen accordingly to the number of input sheets

with k = 5 , 2 new sheets are added with the averaged data of sheets 1 to 5 and 6 to 10

with k = 2 , 5 new sheets are added with the averaged data of sheets 1 to 2 , 3 to 4 ,..., 9 to 10

code :

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

% retrieve raw data
for k=1:nsheets % nsheets
  [data,~,~]=xlsread(filename,sheet_name{k});
  % mean values per sheet
  data_mean_one_sheet(k,:) = mean(data);
end

%% export 
% mean of k successive sheets among nsheets (total)
k = 5;
for ck = 1:nsheets/k
    ind = (1:k)+(ck-1)*k;
    data_mean_k_sheets(ck,:) = mean(data_mean_one_sheet(ind,:));
    xlswrite(filename,data_mean_k_sheets(ck,:),nsheets+ck);
end
    

[Ojo Olusola]

Thank you for the code. It worked, but the result is not my intention. What I want is:

for k = 5;

Time (column) DF_avg (column) DR_avg (column)

where Time is all time series in column A in the excel cell

DF_avg is average of DF data in sheet column B i.e (DF in Sheet1 + DF in Sheet 2 + DF in Sheet 3 + DF in Sheet4 + DF in Sheet5) divided by 5 (in DF_avg column of new sheet 1) and (DF in Sheet6 + DF in Sheet 7 + DF in Sheet8 + DF in Sheet9 + DF in Sheet10) divided by 5 (in DF_avg column of new sheet 2)

DR_avg is average of DR data in sheet column C i.e (DR in Sheet1 + DR in Sheet 2 + DR in Sheet 3 + DR in Sheet4 + DF in Sheet5) divided by 5 (in DF_avg column of new sheet 1) and (DF in Sheet6 + DF in Sheet 7 + DR in Sheet8 + DR in Sheet9 + DR in Sheet10) divided by 5 (in DR_avg column of new sheet 2)

Thanks.

[Mathieu NOE]

ok , got it !

I think this is the correct answer !!

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

% retrieve raw data
for k=1:nsheets % nsheets
  T = readtable(filename,"Sheet",sheet_name{k});
  time = T.Time;
  DF(:,k) = T.DF;
  DR(:,k) = T.DR;  
end

%% export 
% mean of k successive sheets among nsheets (total)
k = 5;
for ck = 1:nsheets/k
    ind = (1:k)+(ck-1)*k;
    DF_avg = mean(DF(:,ind),2); % mean along vertical direction
    DR_avg = mean(DR(:,ind),2); % mean along vertical direction
    out_table = table(time,DF_avg,DR_avg);
    writetable(out_table,filename,"Sheet",nsheets+ck);
end
    

[Ojo Olusola]

I appreciate this code. When I run the code, the second part of the code did not run but I got this error message:

Error in (line 11)

Unrecognized table variable name 'Time'.

help. Thanks.

[Mathieu NOE]

hello

oh, I found the reason why; when I add the two extra sheets , my time variable is with lower case t whereas your original sheets have Time with upper case T.

also my original code woud read the two extra sheets after a first iteration, as we have now more sheets

tht's why I also now I forced nsheets = 10 to be sure the 2 extra sheets are not taken into account in the read process - if you run the script multiple times on the same excel file.

code updated :

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

% retrieve raw data
for k=1:nsheets % nsheets
  T = readtable(filename,"Sheet",sheet_name{k});
  Time = T.Time;
  DF(:,k) = T.DF;
  DR(:,k) = T.DR;  
end

%% export 
% mean of k successive sheets among nsheets (total)
k = 5;
for ck = 1:nsheets/k
    ind = (1:k)+(ck-1)*k;
    DF_avg = mean(DF(:,ind),2); % mean along vertical direction
    DR_avg = mean(DR(:,ind),2); % mean along vertical direction
    out_table = table(Time,DF_avg,DR_avg);
    writetable(out_table,filename,"Sheet",nsheets+ck);
end

[Mathieu NOE]

Hi

if my contribution has helped you, do you mind accepting it ?

tx

[Mathieu NOE]

hello again

problem solved ?

[Ojo Olusola]

Thanks. The problem is yet to be solved. if k=5, I want two sheets with average of sheets 1-5 and sheet 6-10 retaining the original length of 14974 per DR and DF columns and not their means.

i.e data_mean_k_sheets(ck,:) = 14974x2 and not 2x2

Thanks.

Ojo O. S.

[Mathieu NOE]

well

maybe my comments of the code was unclear but the latest code I sent you is doing what your looking for

dimensions DF_avg 14974x1

dimensions DR_avg 14974x1

as you ask

they are average of 5 sheets , keeping the original vertical length of 14974

now you excel file has two more sheets with 14974x2 of data

check it again !

example first additionnal sheet :

clc
clearvars
% Importing Data from excel across multiple sheets.
filename = 'mydata2a.xlsx';
[~,sheet_name]=xlsfinfo(filename);
% nsheets = numel(sheet_name);
nsheets = 10;
% retrieve raw data
for k=1:nsheets % nsheets
  T = readtable(filename,"Sheet",sheet_name{k});
  Time = T.Time;
  DF(:,k) = T.DF;
  DR(:,k) = T.DR;  
end
%% export 
% mean of k successive sheets among nsheets (total)
k = 5;
for ck = 1:nsheets/k
    ind = (1:k)+(ck-1)*k;
    DF_avg = mean(DF(:,ind),2); % mean of k successive sheets
    DR_avg = mean(DR(:,ind),2); % mean of k successive sheets
    out_table = table(Time,DF_avg,DR_avg);
    writetable(out_table,filename,"Sheet",nsheets+ck);
end

[Ojo Olusola]

Dear Mathieu NOE

Thank you very much for your patience with me. The code is working perfectly now.

Can you get me code to execute ANFIS model, Genetic Algorithm (GA) model, RBF. Thanks.

[Mathieu NOE]

hello

glad we finally make it work ! if my contribution has helped you, do you mind accepting it ?

-----------------------------------------------------------------------

For ANFIS model, Genetic Algorithm (GA) model, RBF : I personnally have not make any code or application in that area but there are quite a lot of available ressources and examples on internet / youtube / FEX matlab :

File Exchange - MATLAB Central (mathworks.com)

Microsoft PowerPoint - Lecture17_ANFIS (sharif.edu)

Tune Sugeno-type fuzzy inference system using training data - MATLAB anfis - MathWorks France

Neuro-Adaptive Learning and ANFIS - MATLAB & Simulink - MathWorks France

Anfis Algorithm Code Matlab For Classification PDF | go-pdf.online - Unlimited Books

[Mathieu NOE]

hello

do you mind accepting my answer ?

tx

[Ojo Olusola]

Thank you, your answer is accepted.

[Mathieu NOE]

hello again

sorry , but it seems you didn't press the "accept" button ...

all the best