Another formula solution - but not practical with the number of cells you're
trying to calculate (and also array entered):

=MAX(IF(COUNTIF(A1:A12000,"larry")1,LARGE((A1:A12 000="larry")*ROW(A1:A12000),ROW(INDIRECT("1:"&COUN TIF(A1:A12000,"larry")-1)))-LARGE((A1:A12000="larry")*(ROW(A1:A12000)),ROW(IND IRECT("2:"&COUNTIF(A1:A12000,"larry"))))-1),IF(A1<"larry",MATCH("larry",A1:A12000,0)-1),IF(A12000<"larry",ROWS(A1:A12000)+ROW(A1)-1-MAX(IF(A1:A12000="larry",ROW(A1:A12000),""))))


Change the range reference as needed. It takes a little bit to calculate
(but still under a minute on my machine). Without using any helper columns
or VBA- you almost have to use an array formula, which will be quite a
calculation load. If you have to do all 800 names, I'd look at using a
custom function.



"Vasilis Tergen" wrote:

How would I find the maximum amount of intervals between
"Larry" re- appearing in column A?
I must use a formula- not a macro or a filter.

The correct answer in this example= 4.
That means that in column A, the maximum absence (or intervals)
of "Larry" re- appearing was 4 times, which occured between rows
2 and 7. (Ex: As opposed to rows: 7 - 9, where he was only absent once)


Rows Column A

1 John
2 Larry
3 John
4 John
5 John
6 Mary
7 Larry
8 Mary
9 Larry
10 John