formula to find maximum consecutive positive values by year

[idealist87]

HI

I got data by year and return in column C and D

Googled it and found the maximum consecutive positive and negative value for entire Column C. But now I need to find it by year after year.

Thank you

[wk9128]

Cell H6 formula , Drag down

=MAX(FREQUENCY(IF((--$D$2:$D$106=--G6)*($C$2:$C$106<0),ROW($2:$106)),IF((--$D$2:$D$106=--G6)*($C$2:$C$106>=0),ROW($2:$106))))

Cell I6 formula , Drag down

=MAX(FREQUENCY(IF((--$D$2:$D$106=--G6)*($C$2:$C$106>0),ROW($2:$106)),IF((--$D$2:$D$106=--G6)*($C$2:$C$106<=0),ROW($2:$106))))

[idealist87]

Cheers!!! Thank you

[wk9128]

@idealist87 You're Welcome. Glad to help . Thank You for the feedback and rep.

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".

[idealist87]

hi

I have added one more league to the data.

Same as above, but this want consecutive values based on both year and league.

Thank you

[wk9128]

hi

I have added one more league to the data.

Same as above, but this want consecutive values based on both year and league.

Thank you

Cell H11 formula

=MAX(FREQUENCY(IF((--$D$2:$D$106=--G11)*($B$2:$B$106=G10)*($C$2:$C$106<0),ROW($2:$106)),IF((--$D$2:$D$106=--G11)*($B$2:$B$106=G10)*($C$2:$C$106>=0),ROW($2:$106))))