英文:
Excel Find the Column # When a Value Exceeds a Threshold
问题
I have translated the requested content into Chinese:
-
第一个需要返回特定阈值超过5时的第一列编号（达到阈值的列号）
-
第二个公式需要返回从达到阈值的列开始计数的列数（自达到阈值以来的计数）
-
第三个公式需要计算从达到阈值的列开始未达到阈值的列数（未达到阈值的计数）
| 列编号 | 阈值 | 列 1 | 列 2 | 列 3 | 列 4 | 列 5 | 达到阈值的列号 | 自达到阈值以来的计数 | 未达到阈值的计数 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 20 | 15 | 5 | 25 | 30 | 35 | 列 3 | 3 | 0 |
| 2 | 5 | 15 | 10 | 20 | 25 | 0 | 列 1 | 5 | 1 |
| 3 | 75 | 75 | 90 | 100 | 75 | 30 | 列 2 | 4 | 1 |
| 4 | 55 | 50 | 40 | 35 | 80 | 80 | 列 4 | 2 | 3 |
| 5 | 100 | 101 | 95 | 80 | 100 | 104 | 未达到阈值 | 0 | "" |
英文:
I am in need of three complementary Excel formulas.
-
The first needs to return the first column number when a specific threshold is exceeded by 5 (Threshold Reached Col #)
-
The second formula needs to return the count of columns starting from the column the threshold was reached (Count Since Thresh Reached)
-
The third formula needs to count how many columns did not reach the threshold starting from when the threshold was reached (Count Below Thresh)
| Col Num | Threshold | Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | Thresh Reached Col # | Count Since Thresh Reached | Count Below Thresh |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 20 | 15 | 5 | 25 | 30 | 35 | Col 3 | 3 | 0 |
| 2 | 5 | 15 | 10 | 20 | 25 | 0 | Col 1 | 5 | 1 |
| 3 | 75 | 75 | 90 | 100 | 75 | 30 | Col 2 | 4 | 1 |
| 4 | 55 | 50 | 40 | 35 | 80 | 80 | Col 4 | 2 | 3 |
| 5 | 100 | 101 | 95 | 80 | 100 | 104 | Thresh Not Reached | 0 | "" |
答案1
得分: 1
=IFERROR(INDEX($C$6:$S$6,MATCH(TRUE,INDEX(C7:S7>B7,),0)),"Threshold Not Exceeded")
英文:
To return the column header, try the following formula...
=IFERROR(INDEX($C$6:$S$6,MATCH(TRUE,INDEX(C7:S7>B7,),0)),"Threshold Not Exceeded")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论