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

20 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
Voss
Voss on 25 Jan 2022
Edited: Voss on 25 Jan 2022
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

Sign in to comment.

More Answers (0)

Categories

Find more on Timetables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!