Count max interval of cells with numbers before blank cell in Excel -
is there way count, using formulas, intervals of cells numbers in row (before blank cell appears) , display value of "biggest chain" found? doing research automatization in twitter. have list of n users. there 1 column each hour of research, , in every cell there number of tweets published each user during hour. if user didn't publish in 1 specific hour, cell empty (here x means empty cell):
user 1: x x x 1 1 1 2 1 3 8 4 2 1 3 1 1 1 1 x 2 x
user 2: 1 2 x 2 3 1 3 x x 2 1 8 7 2 1 2 3 x 1 3 x
i conclude user 1 uses kind of automatization because active 15 hours in row.
user 2, although active user 1, active 8 hours in row
with first row of data showing user in a2 , tweet numbers in b2:v2
can use "array formula" in x2
=max(frequency(if(b2:v2<>"",column(b2:v2)),if(b2:v2="",column(b2:v2))))
confirm ctrl+shift+enter , copy down
that gives me 15 , 8 sample data
Comments
Post a Comment