Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dan
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Imported Data creates blank cells that aren't really blank JackieD Excel Worksheet Functions 14 February 23rd 06 01:57 AM
Blank Rows from Merged Cells in Drop Down Menu Kati Excel Discussion (Misc queries) 1 February 20th 06 08:59 PM
Excel inserted 0's in cells linked to blank cells lburg801 Excel Discussion (Misc queries) 5 October 28th 05 11:32 PM
Generating truly blank cells Nat Excel Worksheet Functions 4 September 30th 05 11:39 PM
Copy down - special to fill only the blank cells Mike Excel Discussion (Misc queries) 3 April 18th 05 10:08 PM


All times are GMT +1. The time now is 08:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"