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

4 views (last 30 days)
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.

Accepted Answer

Voss
Voss on 25 Jan 2022
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
  3 Comments

Sign in to comment.

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!