How to compute the maximum series of a specific condition returning true

Question

i have a slight issue to count the MAX frequency of where the third colmn is bigger than the second. This is just a statistic with scores. The issue is that i want to have it in one single formula without a macro.

B   C
------
2   0
1   2
2   1
2   3
0   1
1   2
0   1
3   3
0   2
0   2

i have tried it with:

{=MAX(FREQUENCY(B3:B100;B3:B100>=C3:C100))} to get 1 for B
{=MAX(FREQUENCY(C3:C100;C3:C100>=B3:B100))} to get 7 for C

I excpected it to deliver me the longest series where the value in the one column was bigger than in the other one, but i failed hard...

Answer 1

Try this version to get 7

=MAX(FREQUENCY(IF(C3:C100>=B3:B100,IF(B3:B100<>"",ROW(B3:B100))),IF(C3:C100<B3:B100,ROW(B3:B100))))

confirmed with CTRL+SHIFT+ENTER

obviously reverse the ranges to get your other result

See example here