Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Trying to count the number of blank cells between two points. The problem is
the end point can vary. Cell 1 020w Cell 2 030w Cell 3 Cell 4 Cell 5 Cell 6 Cell 7 070w From the above example, the number of blank cells between cells 1 and 2 is "0". The number of blank cells between 2 and 7 is "5". I need a function that will count blank cells until it finds a non-blank cell and then return me the number of blank cells. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Isn't the number of blanks between 2 and 7 four? Assuming your cell 1 is A1 B1 should be blank In B2 copied down use this formula =IF(A2="","",COUNTIF(A$1:A2,"")-SUM(B$1:B1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
In the example the # of blanks is 4 but the cell, in the example cell7, is
not known. The information entered is random within a column. For example, cells A1=3, A2=75, A75=2. I will not know the content of the cell nor the position is was entered. I need to count the blanks between unknown cells in order to determine where the next cell with data is. From the above example, we see A2 then A75 but in the application I will not know the next value will be in olocation A76 or A350. Ho do I count the blanks, or be able to jump to the next cell with data and take the difference? Thanks Dan "daddylonglegs" wrote: Isn't the number of blanks between 2 and 7 four? Assuming your cell 1 is A1 B1 should be blank In B2 copied down use this formula =IF(A2="","",COUNTIF(A$1:A2,"")-SUM(B$1:B1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Still a bit unclear where you are heading. Are you trying to determine
the row of the last numeric value? Dan wrote: In the example the # of blanks is 4 but the cell, in the example cell7, is not known. The information entered is random within a column. For example, cells A1=3, A2=75, A75=2. I will not know the content of the cell nor the position is was entered. I need to count the blanks between unknown cells in order to determine where the next cell with data is. From the above example, we see A2 then A75 but in the application I will not know the next value will be in olocation A76 or A350. Ho do I count the blanks, or be able to jump to the next cell with data and take the difference? Thanks Dan "daddylonglegs" wrote: Isn't the number of blanks between 2 and 7 four? Assuming your cell 1 is A1 B1 should be blank In B2 copied down use this formula =IF(A2="","",COUNTIF(A$1:A2,"")-SUM(B$1:B1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
I am trying to determine the space between the next row of information. I can
determine the location of the first input, i.e. Cell A2, but the next data input can be A3 or A5 or A87 - I will not know. How do I find the location of the next data input, and therefore know the spacing between inputs? Regards, Dan "Aladin Akyurek" wrote: Still a bit unclear where you are heading. Are you trying to determine the row of the last numeric value? Dan wrote: In the example the # of blanks is 4 but the cell, in the example cell7, is not known. The information entered is random within a column. For example, cells A1=3, A2=75, A75=2. I will not know the content of the cell nor the position is was entered. I need to count the blanks between unknown cells in order to determine where the next cell with data is. From the above example, we see A2 then A75 but in the application I will not know the next value will be in olocation A76 or A350. Ho do I count the blanks, or be able to jump to the next cell with data and take the difference? Thanks Dan "daddylonglegs" wrote: Isn't the number of blanks between 2 and 7 four? Assuming your cell 1 is A1 B1 should be blank In B2 copied down use this formula =IF(A2="","",COUNTIF(A$1:A2,"")-SUM(B$1:B1)) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Did you try the formula I posted? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Yes I did try the formula. Beginning at A1 if there is a value in A2 (which I
do not know if it will be filled) the formula became circular reference and did not work. If I removed the data in A2 and inserted data in A4, the formula did not count the blank spaces between A1 and A4 thereby returning 3. Regards, Dan "daddylonglegs" wrote: Did you try the formula I posted? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Hi Dan, The formula I suggested needs to be put in B2 and copied down the column. If the corresponding entry in column A is a blank it returns a blank, if not it gives a count of the blanks between that row and the previous entry in column A. For example if you have an entry in A1, next in A5 and next in A15 then column B will be empty except for a 3 in B5 (the number of blanks between A1 and A5) and a 9 in B15 (the number of blanks between A5 and A15). Perhaps this isn't quite what you require, I'm sure it can be amended to suit -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Dan
I'm curious....is this the same question you asked in the "Function similar to SHIFT+CTRL+DOWN KEY" thread you posted? Was that one not resolved? Or is that a different "Dan"? *********** Regards, Ron XL2002, WinXP-Pro "Dan" wrote: Yes I did try the formula. Beginning at A1 if there is a value in A2 (which I do not know if it will be filled) the formula became circular reference and did not work. If I removed the data in A2 and inserted data in A4, the formula did not count the blank spaces between A1 and A4 thereby returning 3. Regards, Dan "daddylonglegs" wrote: Did you try the formula I posted? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting blank cells until value is reached
Ron,
You are correct. Since it appeared the question was not clear enough, I reposted the question that explained a little better what I was trying to achieve. Thanks for all the help. Dan "Ron Coderre" wrote: Dan I'm curious....is this the same question you asked in the "Function similar to SHIFT+CTRL+DOWN KEY" thread you posted? Was that one not resolved? Or is that a different "Dan"? *********** Regards, Ron XL2002, WinXP-Pro "Dan" wrote: Yes I did try the formula. Beginning at A1 if there is a value in A2 (which I do not know if it will be filled) the formula became circular reference and did not work. If I removed the data in A2 and inserted data in A4, the formula did not count the blank spaces between A1 and A4 thereby returning 3. Regards, Dan "daddylonglegs" wrote: Did you try the formula I posted? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516708 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
Blank Rows from Merged Cells in Drop Down Menu | Excel Discussion (Misc queries) | |||
Excel inserted 0's in cells linked to blank cells | Excel Discussion (Misc queries) | |||
Generating truly blank cells | Excel Worksheet Functions | |||
Copy down - special to fill only the blank cells | Excel Discussion (Misc queries) |