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

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
M = csvread(fullfile(folder, csvfiles(k).name));
average(k) = mean(M(:,1));
end
>> csvwrite(fullfile(cd, 'Feb 2013 avg.csv'), average);
>> csvwrite(fullfile(folder, 'Feb 2013 avg.csv'), average);

Answer 1

[Harsha Priya Daggubati]

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.

• It is suggested to use 'readmatrix' instead of 'csvread'.

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

[Kiwanee Smith]

Thank you so much.