Streaks in a row

[rhuen]

What if I had:

table.PNG

and I wanted to check three things. Are there numbers of any value greater than 0, in five or more consecutive cells? If so, how many consecutive cells and what is the sum of those numbers? The results would be put into three separate columns.

[MarvinP]

Hi rhuen,

What do I win by doing this problem??? See the answer in the attached.. I made helper columns for the first part and then used them for question 2 and 3.

Sum of more than 5 GT 0 in a row.xlsx

[rhuen]

Hi rhuen,

What do I win by doing this problem??? See the answer in the attached.. I made helper columns for the first part and then used them for question 2 and 3.

Attachment 614035

Reputation, I guess?? Thanks so much. I'm new to Excel and quickly learning so many things thanks to people like you. Hopefully, eventually, I won't have to keep bugging people on forums.

[MarvinP]

Hi,

Thanks for the rep but I keep learning new stuff all the time. I learned how to do "Fill Down" from someone a few days ago. It doesn't come all at once. You need to keep wondering, trying and learning.

BTW = Fill Down was a
1. Select the range
2. Go To Special and select Blanks
3. Put a "=A1" (A1 is the cell above first blank cell) and do a Ctrl+Enter!! Blew my mind!!!

[rhuen]

Hello again,

Any ideas as to how I could go about determining the MAX number in the longest streak if there are multiple streaks? I'm unsure of how to use the MATCH function. We would use that, correct? I'll attach a new file with multiple streaks.

I should probably rename the original post, this isn't really frequency...

I figured out that I could simply change the SUM function to MAX:

=IF(CG2="Yes",MAX(INDIRECT(ADDRESS(ROW(),MATCH(CI2,AQ2:CE2,0))&":"&ADDRESS(ROW(),MATCH(CI2,AQ2:CE2,0)-CI2+1))),"X")