To all parties having participated in this inquiry:

The formula I've been looking for may have not been conceived as
an interpolation of traditional functions.
However, a UDF, similar to the one kindly provided by JMB works.

Insofar as applying this function to my target, statistical report however,
a more interesting conclusion was drawn:

Using arguably one of the most powerful pc's available to the public,
200 cells containing complex formulas such as Mr. Coderres' or JMBs',
managed to halt excel repeatedly and ultimately, permanently.

The report I've created, presents statistical & other information,
regarding an average of 800, ever-changing names.
The report draws information from a separate worksheet,
which contains 12.000 rows and 22 columns of data.
More rows are added daily, averaging 12.000 additional rows/ year.
12000 rows* 22 columns= 264.000 cells.

The resulting report worksheet, contains 800 rows
(one for each name) and 20 columns.
Each of these cells, except for the names, contain mostly complex
array formulas. Thus, resulting in a total of 800*22= 17.600 cells.
I have found that if more than 10 rows of the report are filled with
formulas, (200 cells), excel cannot function.

Thus, regrettably, I now realize how the initial question was doomed from
the start.

Thank you very much for your cooperation.



"Vasilis Tergen" wrote:

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