Thank you for the reply

I tried the formula repeatedly and its' results are as follows:

A) It creates no "freezing" problems whatsoever, due to calculation load,
because of its' simplicity.
B) It doesn't count the needed absences. It instead counts a volatile
frequency between rows containing both "Larry" and "A".
Ex: It will result in a count of 1158 absences (between rows containing
both "Larry" and "A", searching through 12000 rows.
(Your formula had to be slightly altered to produce this result)
C) Again, to fully grasp the concept, imagine that you filter the 12000 rows
for
the name "Larry". Then, 51 rows would remain.
Within those 51 rows, the maximum absence of both:
a) "Larry" appearing under columns "F" or "H"
&
b) "A" appearing under column "S" (on the same row),

would be easily determined as being= 15 times.

Once again, thank you for the much needed assistance.

"Lori" wrote:

Try array-entered (CSE):

=MAX(FREQUENCY(ROW(1:1201),IF((F1:F1200="Larry")+( H1:H1200="Larry"))*
(S1:S1200="A"),ROW(1:1200))))-1

then eplace "A" by "B" or "C" for other results.
Note: there is one extra row in the first argument of the frequency
function


Vasilis Tergen wrote:

Sir,

Thank you very much for taking the time to write that formula.
I tried it repeatedly and these are its' results:

I) It works if the target column (Column A in this case) has less than
7 blank rows
at its' beginning. (Rows 1-7)
II) It may "jam" a high-powered machine if copied and pasted onto other
cells.
III) The function certainly works on the problem I posted, however
as it would've been too complicated to state in my initial
inquiry,
I didn't explain the complete version of the problem -which
follows-
and for which, the afforementioned formula does not suffice:

The Problem

A) Instead of having 1 column to work with, I have 3.
Larry, John or any other name, may appear either under columns F or H,
yet never
simultaneously, under both columns F & H of the same row.

B) Additionally, in order to count the number of absences, criteria must be
used,
from yet another column, column S.
Column S will always contain 1 of either of 3 values: A, B or C.

C) A visual table depiction:


Column F Column H Column S

Larry John A
Mary Mary A
Mary Larry B
John Larry C
Mary John C
Larry Mary A



D) The actual, exact data results needed are as follows:

I must count the maximum absence of:

1) "Larry" appearing under either column F or H, while
simultaneously,
having "A" appear under column S (on the same row).

2) "Larry" appearing under either column F or H, while
simultaneously,
having "B" appear under column S (on the same row).

3) "Larry" appearing under either column F or H, while
simultaneously,
having "C" appear under column S (on the same row).

- Obviously, 3 similar formulas will be used to calculate the
afforementioned,
entered into 3 separate cells.
- Note that the count must ommit rows which do not contain exactly:
(Ex) both: "Larry" and: ("A", or "B" or "C")
The formula you kindly provided me with, includes rows in its'
results, which do
not meet the specified criteria. It simply provides the maximum
absence between
such rows. Ex: In the target workbook, it resulted in a 1438
maximum row absence
between "Larry" appearing under column "F".

-To give you a better idea, "Larry" actually appears a total of
51 times in 12000 rows.
If I were to filter the 12000 rows for "Larry", I would find
that the maximum
absence of both "Larry" and "A" appearing on the same row,
would be= 15 times.
Not 1438, which is the maximum absence of "Larry" simply
appearing under column F.

F) Lastly, should it be of any help:
I recently posted a related question in this discussion forum.
It is to be found under: Excel worksheet functions/ "A rather
difficult .........."
I managed to answer that question on my own, succesfully.

If you were to help me say, define just one the aforementioned rows (in
excel terms)
I would more than likely be able to complete the needed formula.


Thank you very much.


"Ron Coderre" wrote:

Here you go.....
Try something like this:

With
A2:A12000 contains names, with "larry" interspersed

This ARRAY FORMULA returns the largest consecutive gap between "larry" cells
B1:
=MAX(SMALL(IF((A2:A12000="larry")*(A1:A11999<"lar ry")*(A3:A12001<"larry"),ROW(A2:A12000),10^99),RO W($A$2:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry")))))-SMALL(IF((A2:A12000="larry")*(A1:A11999<"larry")* (A3:A12001<"larry"),ROW(A2:A12000),10^99),ROW($A$ 1:INDEX(A:A,SUMPRODUCT(--(A2:A12000="larry")*(A1:A11999<"larry")*(A3:A1200 1<"larry"))-1)))-1)

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Yeah....I know....it's not very elegant,
but it seems to get the job done. <g

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"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