Return Max Number of Consecutive Occurences of '4' or More

[Brumbot]

Greetings all. It's been a while since I've posted.

I have a column of data that contains numbers between 0 and 5. I currently found a formula that will provide me with the max number of consecutive appearances of the number 5. See below:

Please Login or Register  to view this content.

I am looking to change this code subtly, so that it includes consecutive occurrences of 4 OR 5. e.g.

3
4
4
5
4
3
3
4
5
3

Would return 4, since I have 2 4's, a 5 and then another 4 and it is the highest run of this in the column. Can anyone help? All advice is much appreciated.

[XOR LX]

Hi.

=MAX(FREQUENCY(IF(MMULT(0+('Utilisation Sheet'!AE10:AE57={4,5}),{1;1}),ROW('Utilisation Sheet'!AE10:AE57)),IF(MMULT(0+('Utilisation Sheet'!AE10:AE57<>{4,5}),{1;1})=2,ROW('Utilisation Sheet'!AE10:AE57))))

Regards

[martindwilson]

alternative
=MAX(FREQUENCY(IF('Utilisation Sheet'!AE10:AE57<=5,IF('Utilisation Sheet'!AE10:AE57>=4,ROW('Utilisation Sheet'!AE10:AE57))),IF('Utilisation Sheet'!AE10:AE57<>4,IF('Utilisation Sheet'!AE10:AE57<>5,ROW('Utilisation Sheet'!AE10:AE57)))))

[Brumbot]

alternative
=MAX(FREQUENCY(IF('Utilisation Sheet'!AE10:AE57<=5,IF('Utilisation Sheet'!AE10:AE57>=4,ROW('Utilisation Sheet'!AE10:AE57))),IF('Utilisation Sheet'!AE10:AE57<>4,IF('Utilisation Sheet'!AE10:AE57<>5,ROW('Utilisation Sheet'!AE10:AE57)))))

Thanks Martin and XOR LX. Both solutions works great.

Don't mean to be a pain, but do you have a solution for if this was 3, 4 OR 5, not just 4 or 5?

[martindwilson]

oops wrong answer