i have the data like below.
first , i pick up any val >5.0 and count SEL as 1
second, for those SEL to be 1, i want also select adjacent row (SEL=1) , then count them as 2.
Question: since there are tons of rows to check, if it is possible more fast way to find all adjacent rows around SEL=1.
thx in advance
SEL | NUMBER |
---|---|
3.9 | |
3.2 | |
2 | 1.8 |
1 | 5.4 |
2 | 2.45 |
2 | 1.42 |
1 | 5.25 |
2 | 2.4 |
1.42 | |
3.85 |
i have the data like below.
first , i pick up any val >5.0 and count SEL as 1
second, for those SEL to be 1, i want also select adjacent row (SEL=1) , then count them as 2.
Question: since there are tons of rows to check, if it is possible more fast way to find all adjacent rows around SEL=1.
thx in advance
SEL | NUMBER |
---|---|
3.9 | |
3.2 | |
2 | 1.8 |
1 | 5.4 |
2 | 2.45 |
2 | 1.42 |
1 | 5.25 |
2 | 2.4 |
1.42 | |
3.85 |
-BEFORE | **** | **** | **** | -AFTER | **** | ** ** |
---|---|---|---|---|---|---|
NO | SEL | NUM | NO | SEL | NUM | |
1 | 4.5 | 3 | 1.8 | |||
2 | 1.97 | 4 | 1 | 5.4 | ||
3 | 1.8 | 5 | 2.45 | |||
4 | 1 | 5.4 | 6 | 1.42 | ||
5 | 2.45 | 7 | 1 | 5.25 | ||
6 | 1.42 | 8 | 2.4 | |||
7 | 1 | 5.25 | ||||
8 | 2.4 | |||||
9 | 3.65 | |||||
10 | 2.15 |
2 Answers
Reset to default 1Based on comments, you may try-
=CHOOSEROWS(B:B,LET(x,FILTER(ROW(B2:B),A2:A=1),SORT(INDEX(VSTACK(x-1,x+1)))))
Use filter()
and offset()
, like this:
=filter(A2:C11, B2:B11 + offset(B2:B11, -1, 0) + offset(B2:B11, 1, 0))
See filter() and offset().
=FILTER(B2:B,A2:A=1)
? – Harun24hr Commented Jan 18 at 7:46