All,

Column A will contain a variable number of rows that contain values from 1
to 4. I'd like to create a formula in cell B1 that searches column A from
top to bottom for the first occurrence of 3, then searches backwards
(towards row #1) for the first prior instance of 1, then writes out the
number of columns between the two cells.

I got as far as finding the first occurrence of 3 using this formula
"=MATCH(3,A1:A100,0)", but that's as far as I got.

Any help will be greatly appreciated.

Mark D'Agosta

A possible basic formula would be:

=MATCH(3,A1:A100,0)-MAX(IF((ROW(A1:A100)<MATCH(3,A1:A100,0))*(A1:A100= 1),ROW
(A1:A100)))

entered with Ctrl+Shift+Enter rather than just enter since it is an array
formula. This subtracts the closest row with 1 below the row with 3. YOu
might want to add a -1 on the end if you want the intervening number of rows
(you said columns, but I assume you meant rows).

this assumes that there is a row with 1 closer to row1 than the first row
with 3. You would have to add additional checks if this is not the case.

--
Regards,
Tom Ogilvy

Mark D'Agosta wrote in message
et...
All,

Column A will contain a variable number of rows that contain values from 1
to 4. I'd like to create a formula in cell B1 that searches column A from
top to bottom for the first occurrence of 3, then searches backwards
(towards row #1) for the first prior instance of 1, then writes out the
number of columns between the two cells.

I got as far as finding the first occurrence of 3 using this formula
"=MATCH(3,A1:A100,0)", but that's as far as I got.

Any help will be greatly appreciated.

Mark D'Agosta

Tom,

That did the trick! Thanks a million.

Mark


"Tom Ogilvy" wrote in message
...
A possible basic formula would be:


=MATCH(3,A1:A100,0)-MAX(IF((ROW(A1:A100)<MATCH(3,A1:A100,0))*(A1:A100= 1),ROW
(A1:A100)))

entered with Ctrl+Shift+Enter rather than just enter since it is an array
formula. This subtracts the closest row with 1 below the row with 3. YOu
might want to add a -1 on the end if you want the intervening number of
rows
(you said columns, but I assume you meant rows).

this assumes that there is a row with 1 closer to row1 than the first row
with 3. You would have to add additional checks if this is not the case.

--
Regards,
Tom Ogilvy

Mark D'Agosta wrote in message
et...
All,

Column A will contain a variable number of rows that contain values from
1
to 4. I'd like to create a formula in cell B1 that searches column A
from
top to bottom for the first occurrence of 3, then searches backwards
(towards row #1) for the first prior instance of 1, then writes out the
number of columns between the two cells.

I got as far as finding the first occurrence of 3 using this formula
"=MATCH(3,A1:A100,0)", but that's as far as I got.

Any help will be greatly appreciated.

Mark D'Agosta

Tom,

Quick question about the part of your formula that reads:

.... * (A1:A100=1) ...

I not quite sure how this is evaluated in an array formula. Range A1:A100
contains a random mix of 1, 2, and 3. Does this just check to see which
number (1, 2, or 3) is the first number in the range? Can you give a brief
explanation?

Much appreciated.

Mark D'Agosta



"Tom Ogilvy" wrote in message
...
A possible basic formula would be:


=MATCH(3,A1:A100,0)-MAX(IF((ROW(A1:A100)<MATCH(3,A1:A100,0))*(A1:A100= 1),ROW
(A1:A100)))

entered with Ctrl+Shift+Enter rather than just enter since it is an array
formula. This subtracts the closest row with 1 below the row with 3. YOu
might want to add a -1 on the end if you want the intervening number of
rows
(you said columns, but I assume you meant rows).

this assumes that there is a row with 1 closer to row1 than the first row
with 3. You would have to add additional checks if this is not the case.

--
Regards,
Tom Ogilvy

It only returns the row number for rows that have a row number less than the
first row with a 3 in it and the row also contains a 1.

then the max function picks out the highest numbered row fitting these two
conditions.

--
Regards,
Tom Ogilvy

Mark D'Agosta wrote in message
...
Tom,

Quick question about the part of your formula that reads:

... * (A1:A100=1) ...

I not quite sure how this is evaluated in an array formula. Range A1:A100
contains a random mix of 1, 2, and 3. Does this just check to see which
number (1, 2, or 3) is the first number in the range? Can you give a
brief
explanation?

Much appreciated.

Mark D'Agosta



"Tom Ogilvy" wrote in message
...
A possible basic formula would be:



=MATCH(3,A1:A100,0)-MAX(IF((ROW(A1:A100)<MATCH(3,A1:A100,0))*(A1:A100= 1),ROW
(A1:A100)))

entered with Ctrl+Shift+Enter rather than just enter since it is an
array
formula. This subtracts the closest row with 1 below the row with 3.
YOu
might want to add a -1 on the end if you want the intervening number of
rows
(you said columns, but I assume you meant rows).

this assumes that there is a row with 1 closer to row1 than the first
row
with 3. You would have to add additional checks if this is not the
case.

--
Regards,
Tom Ogilvy