最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

google sheets - How to select adjacent cell? - Stack Overflow

programmeradmin6浏览0评论

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
Share Improve this question edited Jan 23 at 7:47 changjx asked Jan 18 at 7:39 changjxchangjx 3972 silver badges9 bronze badges 4
  • Are you looking for =FILTER(B2:B,A2:A=1)? – Harun24hr Commented Jan 18 at 7:46
  • in ur formula, A col will select sel=1. my aim is to sel adjacent row . for example, number 5.4, his adjacent row is 1.8 and 2.45. and number 5.25, his adjacent row is 1.42 n 2.4. that is r-1 / r+1 th rows around rth row. – changjx Commented Jan 18 at 13:36
  • Please edit your question and insert another that shows your manually entered desired results. Also explain why they would be the correct results. – doubleunary Commented Jan 18 at 16:43
  • @changjx Would you please try my answer and feedback. – Harun24hr Commented Jan 19 at 2:12
Add a comment  | 

2 Answers 2

Reset to default 1

Based 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().

发布评论

评论列表(0)

  1. 暂无评论