Counting methods

[gaw1za]

How can you determine the longest run in the following sequence?
1
1
1
1
2
2
1
1
1
2

The answer from a general formula I am looking for here is 4 for "1's"and 2 for "2's".

Also, if you have a list (for example of claims) that go up in increments (after every x days for example), is there a way of determining when last it went up, eg:

1
1
1
1
2
2
3
3
3
3

The formula would for example determine that we went from 2 to 3 claims, 4 days ago. It would determine that we went from 1 to 2 claims, 6 days ago.

Thanks very much!

 

[PaddyD]

given data in a1:a20, and a 1 in B1 (as the target series to count):

longest run:

=MAX(IF(FREQUENCY(IF(A1:A20=1,ROW(A1:A20)),IF(A1:A20<>1,ROW(A1:A20))),FREQUENCY(IF(A1:A20=1,ROW(A1:A20)),IF(A1:A20<>1,ROW(A1:A20)))))

...entered with control + shift + enter, not just enter.

end of increment:

=COUNT(A:A)-MATCH(B1+1,A:A,0)+1

...entered normally

 

[gaw1za]

Thanks

 

[Domenic]

I believe the following would suffice...

=MAX(FREQUENCY(IF(A1:A20=1,ROW(A1:A20)),IF(A1:A20<>1,ROW(A1:A20))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[PaddyD]

"I believe the following would suffice... "

Serves me right for doing a cut & paste!