How to count the values in a column with different lower and upper bounds from excel?

Question

JAGAN MOHAN KUMMARI 2020-4-22

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/519763-how-to-count-the-values-in-a-column-with-different-lower-and-upper-bounds-from-excel

评论： JAGAN MOHAN KUMMARI 2020-4-22

采纳的回答： Johannes Hougaard

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

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Johannes Hougaard 2020-4-22

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/519763-how-to-count-the-values-in-a-column-with-different-lower-and-upper-bounds-from-excel#answer_427550

在 MATLAB Online 中打开

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