How to filter data from columns and extract corresponding x-values in excel files?

Hi,

I have attached a part of my datasheet. I want to filter data >=0.125 under coating column. After filtering I want to pick the smallest value from the filtered data and find the corresponding x-value. For example, for column B, 0.175 is the desired coating value for which the corresponding X is 800. Please advise on the functions I can use to achieve this. When I am trying to filter data, it is also filtering my dilution values.

Answer 1

[Voss]

Here are some ways to do this, depending on exactly what you want to do.

"Filtering" on column B only:

% data = xlsread('Sample data.xlsx'); % use this if you have an older version of MATLAB
data = readmatrix('Sample data.xlsx');

idx = data(:,2) >= 0.125;
data_subset = data(idx,:);
[~,min_idx] = min(data_subset(:,2));
data_subset(min_idx,1)

ans = 800

"Filtering" on each "Coating" column separately:

% data = xlsread('Sample data.xlsx'); % use this if you have an older version of MATLAB
data = readmatrix('Sample data.xlsx');

for i = 2:size(data,2)
    idx = data(:,i) >= 0.125;
    data_subset = data(idx,[1 i]);
    [~,min_idx] = min(data_subset(:,2));
    data_subset(min_idx,1)
end

ans = 800

ans = 1600

ans = 400

ans = 800

ans = 1600

ans = 400

"Filtering" on all "Coating" columns together (note that only the first instance of the minimum value will be used):

% data = xlsread('Sample data.xlsx'); % use this if you have an older version of MATLAB
data = readmatrix('Sample data.xlsx');

idx = [false(size(data,1),1) data(:,2:end) >= 0.125];
[ridx,~] = find(idx);
[~,min_idx] = min(data(idx));
data(ridx(min_idx),1)

ans = 1600

[oshawcole]

Thank you so much for this, this really really helped.

I used the following code:

data = readmatrix('Sample data.xlsx');

for i = 2:size(data,2)
    idx = data(:,i) >= 0.125;
    data_subset = data(idx,[1 i]);
    [~,min_idx] = min(data_subset(:,2));
    data_subset(min_idx,1)
    ans(:,i)=data_subset(min_idx) 
end

But as soon as I reach a column where none of the values were greater than 0.125, it gives me the following error.

"Unable to perform assignment because the size of the left side is 1-by-1 and the size of the right side is 0-by-1".

Any suggestions how I overcome this?

[Voss]

Try this, which checks that there is at least one value >= 0.125 in each column (and don't use ans as a variable, and note that result here has length one less than the size of the table because the first column is treated differently):

data = readmatrix('Sample data.xlsx');
result = NaN(1,size(data,2)-1);
for i = 2:size(data,2)
    idx = data(:,i) >= 0.125;
    if ~any(idx)
        continue
    end
    data_subset = data(idx,[1 i]);
    [~,min_idx] = min(data_subset(:,2));
    result(i-1)=data_subset(min_idx,1);
end
result

result = 1×6

800 1600 400 800 1600 400

[oshawcole]

Thanks again. This solved the problem.