This question is the 2nd part of a question message identically named & posted.

(Continued)
B) Statistic (Formula) 2 needed

a. Column J contains text of one of either of three types: ("IN" or
"OUT" or "GO").
This text, corresponds to the performance of the names appearing
under
columns F & H. ("Larry", "John"...)
b. If "Larry" appeared say, 200 times, in 10.000 rows of both columns F
& H,
& whenever "Larry" appeared, column J read either: IN, OUT or GO.
How would I find the number of rows existant between the identical
repetition
of these events?
("Larry" or "John"... may appear on either columns F or H, yet never
simultaneously, on the same row.)

c. Example

I) Say that "Larry" appeared a total of 200 times in 10.000 rows.
(Under either columns F or H)
II) Also, say that of those 200 appearances, 40 resulted in "IN"
for column J,
60 resulted in "OUT" for column J and 100 resulted in "GO".
III) Assume that "Larry" appeared under column F, on row 1.300 and
on that
row, column J read: "IN".
IV) The next row that "Larry" appeared under, while column J read
"IN",
was: 2.000.
V) Thus, this event was absent for 700 rows.
VI) Later, "Larry" and "IN" appeared simultaneously row 5.000.
Thus, this time, this even was absent for 3.000 rows.

The question is:

How can we find the maximum number of (row) absences
between those events?
(In the afforementioned example, the maximum number of absences=
3.000)

Where are you putting this data? If larry appears 20 times, there will be 20
answers, or do you want an average?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

This question is the 2nd part of a question message identically named & posted.

(Continued)
B) Statistic (Formula) 2 needed

a. Column J contains text of one of either of three types: ("IN" or
"OUT" or "GO").
This text, corresponds to the performance of the names appearing
under
columns F & H. ("Larry", "John"...)
b. If "Larry" appeared say, 200 times, in 10.000 rows of both columns F
& H,
& whenever "Larry" appeared, column J read either: IN, OUT or GO.
How would I find the number of rows existant between the identical
repetition
of these events?
("Larry" or "John"... may appear on either columns F or H, yet never
simultaneously, on the same row.)

c. Example

I) Say that "Larry" appeared a total of 200 times in 10.000 rows.
(Under either columns F or H)
II) Also, say that of those 200 appearances, 40 resulted in "IN"
for column J,
60 resulted in "OUT" for column J and 100 resulted in "GO".
III) Assume that "Larry" appeared under column F, on row 1.300 and
on that
row, column J read: "IN".
IV) The next row that "Larry" appeared under, while column J read
"IN",
was: 2.000.
V) Thus, this event was absent for 700 rows.
VI) Later, "Larry" and "IN" appeared simultaneously row 5.000.
Thus, this time, this even was absent for 3.000 rows.

The question is:

How can we find the maximum number of (row) absences
between those events?
(In the afforementioned example, the maximum number of absences=
3.000)

Sir,

Once again thank you for the reply.
As noted on the other message, this post is the 2nd part of the message.
In regards to your question:
Altough "Larry" may appear a total of 20 times, that isn't of the essence in
this case.
The question asks: How many rows (devoid of "Larry") are there, between rows
containing both "Larry" (Under columns "F" or "H") and "IN" (Under column
"J").

Ex: If both "Larry" and "IN" appear on rows: 1.300, 2.000, 3.000, 5.000 and
9.000,
the answer would be 4.000.

Thus, 4.000 is the maximum (row) absence of "Larry" and "IN" appearing on
the same row.

Thank you.

"John Bundy" wrote:

Where are you putting this data? If larry appears 20 times, there will be 20
answers, or do you want an average?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

This question is the 2nd part of a question message identically named & posted.

(Continued)
B) Statistic (Formula) 2 needed

a. Column J contains text of one of either of three types: ("IN" or
"OUT" or "GO").
This text, corresponds to the performance of the names appearing
under
columns F & H. ("Larry", "John"...)
b. If "Larry" appeared say, 200 times, in 10.000 rows of both columns F
& H,
& whenever "Larry" appeared, column J read either: IN, OUT or GO.
How would I find the number of rows existant between the identical
repetition
of these events?
("Larry" or "John"... may appear on either columns F or H, yet never
simultaneously, on the same row.)

c. Example

I) Say that "Larry" appeared a total of 200 times in 10.000 rows.
(Under either columns F or H)
II) Also, say that of those 200 appearances, 40 resulted in "IN"
for column J,
60 resulted in "OUT" for column J and 100 resulted in "GO".
III) Assume that "Larry" appeared under column F, on row 1.300 and
on that
row, column J read: "IN".
IV) The next row that "Larry" appeared under, while column J read
"IN",
was: 2.000.
V) Thus, this event was absent for 700 rows.
VI) Later, "Larry" and "IN" appeared simultaneously row 5.000.
Thus, this time, this even was absent for 3.000 rows.

The question is:

How can we find the maximum number of (row) absences
between those events?
(In the afforementioned example, the maximum number of absences=
3.000)

I've found the answer to this problem as well.
(In case anyone else finds this information useful)
(This, is the 2nd question in regards to the original subject, which was
answered as well)
This is an array formula, meaning that it must be entered by using:
CTRL, SHIFT, ENTER.

=MAX(IF('Sheet1'!$F$1:$H$50000="Larry",(ROW('Sheet 1'!$F$1:$F$50000)),""))-
(MAX(IF('Sheet1'!$F$1:$H$50000="Larry")*('Sheet1'! $J$1:$J$50000="IN"),
ROW($F$1:$F$50000),"")))-MAX(IF('Sheet1'!$F$1:$H$50000="Larry",
(ROW('Sheet1'!$F$1:$F$50000)),""))-MAX(IF(('Sheet1'!$F$1:$H$50000="Larry")*
('Sheet1'!$J$1:$J50000="IN"),ROW($F$1:$F$50000),"" )))-
COUNTIF((INDIRECT("'Sheet1'!$F&MAX(IF('Sheet1'!$F$ 1:$H$50000="Larry",
(ROW('Sheet1'!$F$1:$H$50000)),""))):INDIRECT("'She et1'!$F"&MAX
(IF(('Sheet1'!$F$1:$H$50000="Larry")*('Sheet1'!$J$ 1:$J$50000="IN"),
ROW($F$1:$H$50000)))),"Larry"))





"Vasilis Tergen" wrote:

Sir,

Once again thank you for the reply.
As noted on the other message, this post is the 2nd part of the message.
In regards to your question:
Altough "Larry" may appear a total of 20 times, that isn't of the essence in
this case.
The question asks: How many rows (devoid of "Larry") are there, between rows
containing both "Larry" (Under columns "F" or "H") and "IN" (Under column
"J").

Ex: If both "Larry" and "IN" appear on rows: 1.300, 2.000, 3.000, 5.000 and
9.000,
the answer would be 4.000.

Thus, 4.000 is the maximum (row) absence of "Larry" and "IN" appearing on
the same row.

Thank you.

"John Bundy" wrote:

Where are you putting this data? If larry appears 20 times, there will be 20
answers, or do you want an average?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

This question is the 2nd part of a question message identically named & posted.

(Continued)
B) Statistic (Formula) 2 needed

a. Column J contains text of one of either of three types: ("IN" or
"OUT" or "GO").
This text, corresponds to the performance of the names appearing
under
columns F & H. ("Larry", "John"...)
b. If "Larry" appeared say, 200 times, in 10.000 rows of both columns F
& H,
& whenever "Larry" appeared, column J read either: IN, OUT or GO.
How would I find the number of rows existant between the identical
repetition
of these events?
("Larry" or "John"... may appear on either columns F or H, yet never
simultaneously, on the same row.)

c. Example

I) Say that "Larry" appeared a total of 200 times in 10.000 rows.
(Under either columns F or H)
II) Also, say that of those 200 appearances, 40 resulted in "IN"
for column J,
60 resulted in "OUT" for column J and 100 resulted in "GO".
III) Assume that "Larry" appeared under column F, on row 1.300 and
on that
row, column J read: "IN".
IV) The next row that "Larry" appeared under, while column J read
"IN",
was: 2.000.
V) Thus, this event was absent for 700 rows.
VI) Later, "Larry" and "IN" appeared simultaneously row 5.000.
Thus, this time, this even was absent for 3.000 rows.

The question is:

How can we find the maximum number of (row) absences
between those events?
(In the afforementioned example, the maximum number of absences=
3.000)

What follows, is a simplified array formula, yielding exactly the same results
as the aforementioned (larger) formula:

=COUNTIF(INDIRECT("'Sheet1'!F"&MAX(IF('Sheet1'!$F$ 1:$H$50000="Larry",
(ROW('Sheet1'$F$1:$F$50000)),""))):INDIRECT("'Shee t1'!H"&MAX(IF
(('Sheet1'!$F$1:$H$50000="Larry")*('Sheet1'!$J$1:$ J$50000="IN"),
ROW($F$1:$F$50000),""))),"Larry")-1



"Vasilis Tergen" wrote:

I've found the answer to this problem as well.
(In case anyone else finds this information useful)
(This, is the 2nd question in regards to the original subject, which was
answered as well)
This is an array formula, meaning that it must be entered by using:
CTRL, SHIFT, ENTER.

=MAX(IF('Sheet1'!$F$1:$H$50000="Larry",(ROW('Sheet 1'!$F$1:$F$50000)),""))-
(MAX(IF('Sheet1'!$F$1:$H$50000="Larry")*('Sheet1'! $J$1:$J$50000="IN"),
ROW($F$1:$F$50000),"")))-MAX(IF('Sheet1'!$F$1:$H$50000="Larry",
(ROW('Sheet1'!$F$1:$F$50000)),""))-MAX(IF(('Sheet1'!$F$1:$H$50000="Larry")*
('Sheet1'!$J$1:$J50000="IN"),ROW($F$1:$F$50000),"" )))-
COUNTIF((INDIRECT("'Sheet1'!$F&MAX(IF('Sheet1'!$F$ 1:$H$50000="Larry",
(ROW('Sheet1'!$F$1:$H$50000)),""))):INDIRECT("'She et1'!$F"&MAX
(IF(('Sheet1'!$F$1:$H$50000="Larry")*('Sheet1'!$J$ 1:$J$50000="IN"),
ROW($F$1:$H$50000)))),"Larry"))





"Vasilis Tergen" wrote:

Sir,

Once again thank you for the reply.
As noted on the other message, this post is the 2nd part of the message.
In regards to your question:
Altough "Larry" may appear a total of 20 times, that isn't of the essence in
this case.
The question asks: How many rows (devoid of "Larry") are there, between rows
containing both "Larry" (Under columns "F" or "H") and "IN" (Under column
"J").

Ex: If both "Larry" and "IN" appear on rows: 1.300, 2.000, 3.000, 5.000 and
9.000,
the answer would be 4.000.

Thus, 4.000 is the maximum (row) absence of "Larry" and "IN" appearing on
the same row.

Thank you.

"John Bundy" wrote:

Where are you putting this data? If larry appears 20 times, there will be 20
answers, or do you want an average?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Vasilis Tergen" wrote:

This question is the 2nd part of a question message identically named & posted.

(Continued)
B) Statistic (Formula) 2 needed

a. Column J contains text of one of either of three types: ("IN" or
"OUT" or "GO").
This text, corresponds to the performance of the names appearing
under
columns F & H. ("Larry", "John"...)
b. If "Larry" appeared say, 200 times, in 10.000 rows of both columns F
& H,
& whenever "Larry" appeared, column J read either: IN, OUT or GO.
How would I find the number of rows existant between the identical
repetition
of these events?
("Larry" or "John"... may appear on either columns F or H, yet never
simultaneously, on the same row.)

c. Example

I) Say that "Larry" appeared a total of 200 times in 10.000 rows.
(Under either columns F or H)
II) Also, say that of those 200 appearances, 40 resulted in "IN"
for column J,
60 resulted in "OUT" for column J and 100 resulted in "GO".
III) Assume that "Larry" appeared under column F, on row 1.300 and
on that
row, column J read: "IN".
IV) The next row that "Larry" appeared under, while column J read
"IN",
was: 2.000.
V) Thus, this event was absent for 700 rows.
VI) Later, "Larry" and "IN" appeared simultaneously row 5.000.
Thus, this time, this even was absent for 3.000 rows.

The question is:

How can we find the maximum number of (row) absences
between those events?
(In the afforementioned example, the maximum number of absences=
3.000)