计算excel行中的连续条纹 [英] calculate consecute streak in excel row
问题描述
这些列的值为1到200.
示例:
John Doe 14 16 25 18 40 65 101 85 14 19 18 9 3
Jane Doe 24 22 18 5 8 22 17 17 15 2 1 5 22
Jim Doe 40 72 66 29 25 28
Jan Doe 27 82 22 17 18 9 6 7 9 13
对于每一行,我试图找到当前条纹,最长条纹。
值必须为= = 24。数据从左到右。
John:Current 2;长5
简:当前13;长13
吉姆:当前0; Long 0
Jan:当前0; Long 8
在同一行的自己单元格中计算当前和长期的公式是什么(必须在数据之前)?
对于当前运行,假设C2:Z2中的数据,请尝试此数组公式:
= IFERROR(MATCH(TRUE,C2:Z2> 24,0)-1,COUNT(C2:Z2))
使用 CTRL + SHIFT + ENTER
根据您的评论中使用的单元格引用,尝试此版本最长的条纹
= MAX FREQUENCY(IF(P7:BB7 <= 24,COLUMN(P7:BB7)),IF(P7:BB7> 24,COLUMN(P7:B B7))))
再次确认使用 CTRL + SHIFT + ENTER
或允许范围内的空白(将结束连续),您可以使用此版本
= MAX(FREQUENCY(IF (P7:BB7<> 中,IF(P7:BB7< = 24,COLUMN(P7:BB7))),IF((P7:BB7 = )+(P7:BB7> 24),COLUMN(P7 :BB7))))
I am trying to calculate 2 values. Current Streak and Long Streak.
each record is on 1 row and contains a name and values
each of those columns has a value from 1 to 200. Example:
John Doe 14 16 25 18 40 65 101 85 14 19 18 9 3
Jane Doe 24 22 18 5 8 22 17 17 15 2 1 5 22
Jim Doe 40 72 66 29 25 28
Jan Doe 27 82 22 17 18 9 6 7 9 13
For each row, I'm trying to find the "current" streak and "longest" streak. The values have to be <= 24 to be counted. Data goes left to right.
John: Current 2; Long 5
Jane: Current 13; Long 13
Jim: Current 0; Long 0
Jan: Current 0; Long 8
What would be a formula to calculate the current and long in their own cell on that same row (would have to go before data)?
For current run, assuming data in C2:Z2, try this array formula:
=IFERROR(MATCH(TRUE,C2:Z2>24,0)-1,COUNT(C2:Z2))
Confirm with CTRL+SHIFT+ENTER
For longest streak try this version based on the cell references used in your comment
=MAX(FREQUENCY(IF(P7:BB7<=24,COLUMN(P7:BB7)),IF(P7:BB7>24,COLUMN(P7:BB7))))
Again confirm with CTRL+SHIFT+ENTER
or to allow blanks in the range (which would end a streak) you can use this version
=MAX(FREQUENCY(IF(P7:BB7<>"",IF(P7:BB7<=24,COLUMN(P7:BB7))),IF((P7:BB7="")+(P7:BB7>24),COLUMN(P7:BB7))))
这篇关于计算excel行中的连续条纹的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!