CODEWITHSUNDEEP
excelcountfrequency
user3043166
user3043166

Reputation: 21

Count chains of 0's

I have 800 rows of unique user inputs numbered 0-100. Each user joined on a different day but the dates (1 day for each column) measured include the first possible join date and the last possible, so that for some users there are many 0's until they join and begin submitting numbers, whereas there are also some users who joined then left so they have many 0 entries at the tail end of their row. Most users have some chains of 0's in the middle where they didn't participate for any number of days in the middle and there can of course be more than one of these for a given user. (you can see what I'm probably not explaining well in the attached image).

Basically I want to count what the largest chain of 0's (or in my case, absence) is for each user. That being said, there are some really large chains for a user who registered recently that I want to disregard so I need to be able to count the largest chain starting when a user joins or in this chart first has a number entry other than 0 for any given date.

enter image description here

Upvotes: 2

Views: 147

Answers (1)

barry houdini
barry houdini

Reputation: 46451

To count the longest run of zeroes in B2:CZ2, after the first non-zero number in that range, then you can use this "array formula"

=MAX(FREQUENCY(IF(B2:CZ2=0,IF(B2:CZ2<>"",IF(COLUMN(B2:CZ2)>MIN(IF(B2:CZ2>0,COLUMN(B2:CZ2))),COLUMN(B2:CZ2)))),IF(B2:CZ2>0,COLUMN(B2:CZ2))))

confirmed with CTRL+SHIFT+ENTER

Upvotes: 4

Related Questions