Sir,

Your reply is greatly appreciated.

I invoked the formula you provided repeatedly.
Unfortunately, its' calculation result is not the desired one.
A) In a blank worksheet, containing only column A,
which in turn, contains "Larry" & other, various names, totalling 29
rows,
it results in a "11971" count.
B) In the target workbook, the results are of even greater disparity.
C) I can't be entirely certain as of the error in calculation, as the
formula in
question repeats COUNTIF commands, using discretionary (& or volatile)
references.

P.S. I am sorry I couldn't reply sooner. However, I had (& have) to
reply to all others which graciously offered a reply to this
post, in the order
that they did. (Including thoroughly testing their replies
respectively)


"JMB" wrote:

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