# How can I get the average of one column from multiple Excel files and save the data as a new Excel file?

5 views (last 30 days)
Kiwanee Smith on 23 Jul 2020
Commented: Kiwanee Smith on 29 Jul 2020
Good day,
I had been trying to create a code that would look into seven files and average out the first column over the seven files, then compile the results into a new Excel file. The issue I'm having is that I want to show all three columns of data (column 1 is insolation data, column 2 is latitude, and column 3 is longitude) in my results. Also, my results come out as an overall average of all the numbers. Is there a way for me to get the average to work (ie. average the first number (column 1) in each of the seven files, the the second number (column 1) in each file,etc.) so that my result comes out as the same spreadsheet size (the seven files are 1103x3 and I'm tryiny to get my results to be 1103x3)?
This is what I had so far.
>> csvfiles = dir(fullfile(folder, '*.csv'));
>> numfiles = length(csvfiles);
>> average = zeros(1,numfiles);
>>
>> for k = 1:numfiles
average(k) = mean(M(:,1));
end
>> csvwrite(fullfile(cd, 'Feb 2013 avg.csv'), average);
>> csvwrite(fullfile(folder, 'Feb 2013 avg.csv'), average);

Harsha Priya Daggubati on 27 Jul 2020
Hi,
From your question I get that, you want to find the mean of each cell from all the excel files and then populate a new excel file with the averages calculated.
I can suggest the following:
1. Loop through all the csv files.
2. Use 'readmatrix' to read the data from csv, store it in a variable 'M'. Add the read data in each iteration to M.
3. After the data is read, divide M by number of csv files. This will give the average of each cell.
4. Then write into a file using 'csvwrite'.
for k = 1:numfiles %1
M = M + readmatrix(fullfile(folder, csvfiles(k).name)); %2
M = M/numfiles; %3
csvwrite(fullfile(cd, 'Feb 2013 avg.csv'), M); %4
end

#### 1 Comment

Kiwanee Smith on 29 Jul 2020
Thank you so much.