3

I have a list of numbers (ex. 5, 7, 10, 11, etc.) and 0s in excel rows from D2:BH2, and I want to calculate the longest streak of 0s in each row (with 2 conditions).

The first condition is to ignore streaks that start the row with 0

Ex. (0 0 0 1 5 6 0 0 1) -> this would have a longest streak of 2 instead of 3 due to the first condition

Ex. ('1 0 0 0 1 5 6 0 0 1') -> this would have a longest streak of '3'

The second condition is to ignore streaks that end the row with 0

Ex. (0 1 5 6 0 0 1 0 0 0 0 ) -> this would have a longest streak of 2 instead of 4 due to the second condition.

Ex. (' 0 1 5 6 0 0 1 0 0 0 0 1') -> this would have a longest streak of '4'

Is there a simple way of calculating the streak of 0s based on these two conditions for each row (in one cell formula)?

Currently I'm using a formula:

=MAX(FREQUENCY(IF(D2:BH2=0,COLUMN(D2:BH2)),IF(D2:BH2=0,0,COLUMN(D2:BH2)

This calculates the longest streak; however, does not take into account the two conditions.

Hobo
  • 31
  • 3

2 Answers2

3

Based on this answer where we could trim only leading/trailing spaces, you could try:

enter image description here

Formula in L1:

=BYROW(A1:J4,LAMBDA(a,LET(x,CONCAT(SIGN(a)),y,TEXTSPLIT(x,0,,1),IFERROR(MAX(LEN(DROP(DROP(TEXTSPLIT(0&x&0,,y),1),-1))),0))))
JvdV
  • 70,606
  • 8
  • 39
  • 70
2

This also seems to work if you modify the frequency formula slightly so that the counts of all leading and trailing zeroes are gathered into the first and last cells of the Frequency array then drop those cells:

=MAX(DROP(DROP(FREQUENCY(IF(A1:J1=0,COLUMN(A1:J1)),IF(A1:J1<>0,COLUMN(A1:J1))),1),-1))

enter image description here

If you have all zeroes or only one non-zero value it will error but the correct answer should be zero so updated formula should be:

=IFERROR(MAX(DROP(DROP(FREQUENCY(IF(A1:J1=0,COLUMN(A1:J1)),IF(A1:J1<>0,COLUMN(A1:J1))),1),-1)),0)

enter image description here

Of course you can byrow it:

=BYROW(A1:J6,LAMBDA(r,IFERROR(MAX(DROP(DROP(FREQUENCY(IF(r=0,COLUMN(r)),IF(r<>0,COLUMN(r))),1),-1)),0)))
Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • Yeah, I think it is OK with the caveat that you mention. In the two cases where there are all zeroes or only one non-zero value the correct answer should be zero so I think just an iferror should fix it. – Tom Sharpe Feb 15 '23 at 19:55