CODEWITHSUNDEEP
excelexcel-formulaexcel-2010excel-2007
kylemac
kylemac

Reputation: 189

calculate consecute streak in excel row

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)?

Upvotes: 1

Views: 4524

Answers (1)

barry houdini
barry houdini

Reputation: 46361

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:B‌​B7))))

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))))

Upvotes: 3

Related Questions