How do I isolate part of an array (trying to isolate non-zero consecutive values)

I have a huge problem. I would like to isolate part of an array where the value is 1 for several consecutive months. I know how to calculate the maximum number of consecutive months but I would like to find out when this started. For example see below:

1. The first row I know there are 8 consecutive months and it starts on April.
2. The second row, there are two groups of consecutive months, but I am currently interested in the bigger group of 4 elements. I would like for it to spit out "september"

Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec
1 1 1 1 1 1 1 1
1 1 1 1 1 1 1

Thanks! This is my first post so I hope this isn't a silly question.

What is your expected result?

Hi.

Array formula** for row 2:

=INDEX(A$1:L$1,,MATCH(MAX(FREQUENCY(IF(A2:L2=1,COLUMN(A2:L2)),IF(A2:L2<>1,COLUMN(A2:L2),0))),FREQUENCY(IF(A2:L2=1,COLUMN(A2:L2)),IF(A2:L2<>1,COLUMN(A2:L2),0)),0)-MAX(FREQUENCY(IF(A2:L2=1,COLUMN(A2:L2)),IF(A2:L2<>1,COLUMN(A2:L2),0))))

Copy down as required.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Originally Posted by leena1126

You're amazing!

Ok second question....in the next column, how can I get the next month start of a consecutive array. The result I would get for the first array would not be applicable, but for the second array I would get January.

Is this only ever going to be required for the first and second largest streaks? Or are you potentially going to be wanting to extend this for any number of streaks?

Regards

Does the second of those not require a fourth column to list "January"? Or are strings of a single month only not to be considered?

Regards

Thanks.

=IF(COLUMNS($A:A)>SUM(0+(FREQUENCY(IF($A2:$L2=1,COLUMN($A2:$L2)),IF($A2:$L2<>1,COLUMN($A2:$L2),0))>0)),"",INDEX($A$1:$L$1,,MATCH(LARGE(FREQUENCY(IF($A2:$L2=1,COLUMN($A2:$L2)),IF($A2:$L2<>1,COLUMN($A2:$L2),0))+1/(10^3*ROW(INDIRECT("1:"&1+COLUMNS($A2:$L2)))),COLUMNS($A:A)),FREQUENCY(IF($A2:$L2=1,COLUMN($A2:$L2)),IF($A2:$L2<>1,COLUMN($A2:$L2),0))+1/(10^3*ROW(INDIRECT("1:"&1+COLUMNS($A2:$L2)))),0)-LARGE(FREQUENCY(IF($A2:$L2=1,COLUMN($A2:$L2)),IF($A2:$L2<>1,COLUMN($A2:$L2),0)),COLUMNS($A:A))))

Regards

I should've mentioned that this formula is to be array-entered somewhere in row 2 and then copied to the right until you start to get blanks for the results.

Regards

Originally Posted by leena1126

just saw your latest reply....still confused.

start on the second row. then copy to the right. yep, it's obvious it's a ctrl-shift-enter formula. thanks for the friendly reminder though.

I apologize for the unintended condescension.

Originally Posted by leena1126

Totally confused since you use both rows here. I'm not sure what to substitute in...also not sure why column A is used.

I'm not sure what you mean. I based this on your attachment. The formula I provided is placed somewhere in row 2, e.g. M2, and then copied to the right.

It can also be copied down to give similar results for the entries in row 3.

Regards

Glad you got there in the end.

And you're welcome!