how many rows away from a number to a space below
 

In column b there are either a 1 in the cell or a blank. In column A there
is either a number or blank cell. Is there a function that will say when
there is a 1 in cell b and a blank cell below that 1 in column a how many
rows below is the blank cell.
A B
3 1
5 1
2
3

5

in the example above the 1 in column b next to the 5 from column A is 3 rows
away from the first blank cell in column A. I want the function to find the
first blank cell going down in the row of column A.
Thanks, Eric

how many rows away from a number to a space below
 

Try this array formula

=IF(B1=1,IF(NOT(MIN(IF(A1:$A$6="",ROW(A1:$A$6)))), "",MIN(IF(A1:$A$6="",ROW(A1:$A$6)))-ROW()-1),"")

--
__________________________________
HTH

Bob

"wolfgangea" wrote in message
...
In column b there are either a 1 in the cell or a blank. In column A
there
is either a number or blank cell. Is there a function that will say when
there is a 1 in cell b and a blank cell below that 1 in column a how many
rows below is the blank cell.
A B
3 1
5 1
2
3

5

in the example above the 1 in column b next to the 5 from column A is 3
rows
away from the first blank cell in column A. I want the function to find
the
first blank cell going down in the row of column A.
Thanks, Eric

how many rows away from a number to a space below
 

Does this array-entered** formula do what you want (change the top end of
the range, the A1000, to suit your needs)?

=MIN(IF(INDIRECT("A"&D1&":A1000")="",ROW(INDIRECT( "A"&D1&":A1000")),""))-COUNT(B:B)

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"wolfgangea" wrote in message
...
In column b there are either a 1 in the cell or a blank. In column A
there
is either a number or blank cell. Is there a function that will say when
there is a 1 in cell b and a blank cell below that 1 in column a how many
rows below is the blank cell.
A B
3 1
5 1
2
3

5

in the example above the 1 in column b next to the 5 from column A is 3
rows
away from the first blank cell in column A. I want the function to find
the
first blank cell going down in the row of column A.
Thanks, Eric

how many rows away from a number to a space below
 

=IF(B1=1,MATCH(TRUE,INDEX(A1:$A$6="",),)-2,"")

Normally ENTER


"wolfgangea" wrote:

In column b there are either a 1 in the cell or a blank. In column A there
is either a number or blank cell. Is there a function that will say when
there is a 1 in cell b and a blank cell below that 1 in column a how many
rows below is the blank cell.
A B
3 1
5 1
2
3

5

in the example above the 1 in column b next to the 5 from column A is 3 rows
away from the first blank cell in column A. I want the function to find the
first blank cell going down in the row of column A.
Thanks, Eric