How to count the values in a column with different lower and upper bounds from excel?
1 visualización (últimos 30 días)
Mostrar comentarios más antiguos
JAGAN MOHAN KUMMARI
el 22 de Abr. de 2020
Comentada: JAGAN MOHAN KUMMARI
el 22 de Abr. de 2020
Hi all,
I am trying to find solution for the following problem
Example:
D=xlsread('data.xlsx','sheet1')
a=D(:,2:2:6) % Col B: [1 2 3 4 5 6 7 8 9 10 11 12 18 20 21 22 28 29], Col D: [1 5 9 11 12 20 29], Col F: [7 9 15 17 19 21 22 28 29 30]
lb=a(:,9) % Lower bound values read from Col I: [1 10 20]
ub=a(:,11) % Upper bound values read from Col K: [10 20 30]
ColB must be read first and the count based on each of the lower and upper bound ranges must be given as an output, and the loop must then read the data in Column 4 to again give output based on the condition of different lower and upper bound ranges.
Solution:
Col B: [9 4 5]
Col D: [3 2 2]
Col F: [2 3 4]
What I tried is to get these values reading each column for different ranges seperately.
r1= sum(a(:,2)>=1 & a(:,2)<10)
r2=sum(a(:,2)>=10 & a(:,2)<20)
r3=sum(a(:,2)>=20 & a(:,2)<30)
Solution: [9 4 5]
did this again for Col D, and Col F (similar process in using Excel)
Highly appreciate for making this simple with a loop.
Kind Regards,
Jagan
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/286521/image.jpeg)
0 comentarios
Respuesta aceptada
Johannes Hougaard
el 22 de Abr. de 2020
I think it will be solved by using this snippet
D = xlsread('data.xlsx','sheet1');
a = D(:,1:2:6); %or 2:2:6 as you proposed...
limits = D(:,[7 9]); % or 8 10
limits(all(isnan(limits),2),:) = [];
for ii = 1:size(a,2)
for jj = 1:size(limits,1)
r(ii,jj) = sum(a(:,ii) < max(limits(jj,:)) & a(:,ii) >= min(limits(jj,:)));
end
end
Más respuestas (0)
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!