USA: +1 (725) 712-1730 
Sign in
Using Matlab
Write a function ret=excel_columnaverage(filename,column) that takes an Excel file’s name, and a column id (or name) and returns the average of the numbers in that column. If column is a number, it directly denotes the column number. If column is a string, you need to find a case-insensitive match in the first header row to determine the column number. When taking the average of the values in a column, any entry not containing a number should be ignored. Hints: Matlab’s xlsread() function returns three output arguments: num,txt,raw. It is best to use raw output to solve this problem. If the input column variable is a string, use the first row of raw to find the column number. Once you identify the column number, extract the requested column into a vector rawvector. Write a for loop that goes over each element of the rawvector and collects the entries containing a valid number into a vector v . Only consider entries that contain a number (isnumeric()), and are not NaN (isnan()). After the loop, you can calculate the average of the numbers accumulated in v . >> %crps_data.xlsx is available from: http://sacan.biomed.drexel.edu/ftp/bmeprog/crps_data.xlsx >> %but your function should work for any Excel file. >> %don’t download that file in your code. Assume that the file specified in the first arguments exists. >> disp(xls_columnaverage(‘crps_data.xlsx’,2)) 44.8197 >> disp(xls_columnaverage(‘crps_data.xlsx’,’bmi’)) 27.2189
Expert Answer
data = [];
size = 1;
[numread,textread,rawread] = xlsread(filename);
if isnumeric(column) == 0
column = strcmp(rawread(1,:), column);
end
raw_data = rawread(2:end,column);
for i = 1:length(raw_data)
if isnumeric(raw_data{i}) && ~isnan(raw_data{i})
data(size) = raw_data{i};
size =size+1;
end
end
ret = mean(data);
end
