Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why does blank space appear at end of number? | Excel Discussion (Misc queries) | |||
Number of space in a string | Excel Discussion (Misc queries) | |||
inserting a space in a number | Excel Worksheet Functions | |||
BLANK SPACE TO A NUMBER | Excel Worksheet Functions | |||
Autofit rows - white space. | Excel Discussion (Misc queries) |