MATLAB: Cleaning data from excel file

#data cleaning #dependent

Hi,
I need to clean data from an excel sheet which has is 11 x 78.
There are 3 columns that I am interested in 2, 3, and 9.
The data from column 2 is dependent on the data from column 9. If any of the cells in column 9 is equal to 1 then I need the corresponding cells in columns 2 and 3 to be deleted/left empty/marked as NaN.
Then if any of the cells left in column 2 are equal to zero I need the corresponing cells in column 3 to be deleted/left empty/marked as NaN. I then need the remaining cells in column 2 to be summed and the remaining cells in column 3 to be averaged.
Is this possible?
So far I have tried the below code to try and remove the unwated cells from column 2 but I am not having much look. I am reletaviely new to MATLAB and just cant seem to get this right.
[~, ~,dat] = xlsread('filename');
dat = [dat(:,2) dat(:,3) dat(:,9)];
if (dat(:,2) == 1 && dat(:,9) == 1)
dat(:,2) = [];
dat(:,9) = [];
end

Best Answer

If any of the cells in column 9 is equal to 1 then I need the corresponding cells in columns 2 and 3 to be deleted/left empty/marked as NaN.
dat(dat(:,9) == 1, [2,3]) = nan;
Then if any of the cells left in column 2 are equal to zero I need the corresponing cells in column 3 to be deleted/left empty/marked as NaN.
Apply the same logic as above.
I then need the remaining cells in column 2 to be summed and the remaining cells in column 3 to be averaged.
sum(dat(:,2),'omitnan')
mean(dat(:,3),'omitnan')
Related Question