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

4 views (last 30 days)
oshawcole
oshawcole on 25 Jan 2022
Commented: oshawcole on 25 Jan 2022
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

_
_ 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!