Sir,
Thank you for the reply, it is greatly appreciated.
However, the data table I'm using, contains 12.000 rows of data and
800 names such as "Larry", "John"...
I did something similar to what you've suggested previously, which
unfortunately
resulted in "freezing" the computer, as I needed:
800 names* 12.000 rows containing a "helper" column to analyze the data.
Thus, I'm looking for a formula to be entered in a single cell.
"Bernard Liengme" wrote:
I can do it with a helper column
With the names in A1:A7
In B1 enter: =--(A1="Larry")
In B2 enter: =(B1+(A2<"Larry"))*(A2<"Larry")
Copy this down the column
=MAX(B1:B7) returns the value 4
So experimenting with an array formula, this seems to work
=MAX((B1:B6+(A2:A7<"Larry"))*(A2:A7<"Larry"))
entered with CTRL+SHIFT+ENTER
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Vasilis Tergen" wrote in message
...
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