software

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
quot;0quot;. The number of blank cells between 2 and 7 is quot;5quot;.

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.


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=quot;quot;,quot;quot;,COUNTIF(A$1:A2,quot;quot;)-SUM(B$1:B1))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=516708In 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

quot;daddylonglegsquot; wrote:

gt;
gt; Isn't the number of blanks between 2 and 7 four?
gt;
gt; Assuming your cell 1 is A1
gt;
gt; B1 should be blank
gt;
gt; In B2 copied down use this formula
gt;
gt; =IF(A2=quot;quot;,quot;quot;,COUNTIF(A$1:A2,quot;quot;)-SUM(B$1:B1))
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=516708
gt;
gt;

Still a bit unclear where you are heading. Are you trying to determine
the row of the last numeric value?

Dan wrote:
gt; In the example the # of blanks is 4 but the cell, in the example cell7, is
gt; not known. The information entered is random within a column. For example,
gt; cells A1=3, A2=75, A75=2.
gt;
gt; I will not know the content of the cell nor the position is was entered. I
gt; need to count the blanks between unknown cells in order to determine where
gt; the next cell with data is. From the above example, we see A2 then A75 but in
gt; the application I will not know the next value will be in olocation A76 or
gt; A350.
gt;
gt; Ho do I count the blanks, or be able to jump to the next cell with data and
gt; take the difference?
gt;
gt; Thanks
gt;
gt; Dan
gt;
gt; quot;daddylonglegsquot; wrote:
gt;
gt;
gt;gt;Isn't the number of blanks between 2 and 7 four?
gt;gt;
gt;gt;Assuming your cell 1 is A1
gt;gt;
gt;gt;B1 should be blank
gt;gt;
gt;gt;In B2 copied down use this formula
gt;gt;
gt;gt;=IF(A2=quot;quot;,quot;quot;,COUNTIF(A$1:A2,quot;quot;)-SUM(B$1:B1))
gt;gt;
gt;gt;
gt;gt;--
gt;gt;daddylonglegs
gt;gt;------------------------------------------------------------------------
gt;gt;daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt;gt;View this thread: www.excelforum.com/showthread...hreadid=516708
gt;gt;
gt;gt;

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

quot;Aladin Akyurekquot; wrote:

gt; Still a bit unclear where you are heading. Are you trying to determine
gt; the row of the last numeric value?
gt;
gt; Dan wrote:
gt; gt; In the example the # of blanks is 4 but the cell, in the example cell7, is
gt; gt; not known. The information entered is random within a column. For example,
gt; gt; cells A1=3, A2=75, A75=2.
gt; gt;
gt; gt; I will not know the content of the cell nor the position is was entered. I
gt; gt; need to count the blanks between unknown cells in order to determine where
gt; gt; the next cell with data is. From the above example, we see A2 then A75 but in
gt; gt; the application I will not know the next value will be in olocation A76 or
gt; gt; A350.
gt; gt;
gt; gt; Ho do I count the blanks, or be able to jump to the next cell with data and
gt; gt; take the difference?
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; Dan
gt; gt;
gt; gt; quot;daddylonglegsquot; wrote:
gt; gt;
gt; gt;
gt; gt;gt;Isn't the number of blanks between 2 and 7 four?
gt; gt;gt;
gt; gt;gt;Assuming your cell 1 is A1
gt; gt;gt;
gt; gt;gt;B1 should be blank
gt; gt;gt;
gt; gt;gt;In B2 copied down use this formula
gt; gt;gt;
gt; gt;gt;=IF(A2=quot;quot;,quot;quot;,COUNTIF(A$1:A2,quot;quot;)-SUM(B$1:B1))
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;--
gt; gt;gt;daddylonglegs
gt; gt;gt;------------------------------------------------------------------------
gt; gt;gt;daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; gt;gt;View this thread: www.excelforum.com/showthread...hreadid=516708
gt; gt;gt;
gt; gt;gt;
gt;


Did you try the formula I posted?--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=516708Yes 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

quot;daddylonglegsquot; wrote:

gt;
gt; Did you try the formula I posted?
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=516708
gt;
gt;


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: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=516708Dan

I'm curious....is this the same question you asked in the quot;Function similar
to SHIFT CTRL DOWN KEYquot; thread you posted? Was that one not resolved? Or is
that a different quot;Danquot;?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Danquot; wrote:

gt; Yes I did try the formula. Beginning at A1 if there is a value in A2 (which I
gt; do not know if it will be filled) the formula became circular reference and
gt; did not work. If I removed the data in A2 and inserted data in A4, the
gt; formula did not count the blank spaces between A1 and A4 thereby returning 3.
gt;
gt; Regards,
gt;
gt; Dan
gt;
gt; quot;daddylonglegsquot; wrote:
gt;
gt; gt;
gt; gt; Did you try the formula I posted?
gt; gt;
gt; gt;
gt; gt; --
gt; gt; daddylonglegs
gt; gt; ------------------------------------------------------------------------
gt; gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=516708
gt; gt;
gt; gt;

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.

Danquot;Ron Coderrequot; wrote:

gt; Dan
gt;
gt; I'm curious....is this the same question you asked in the quot;Function similar
gt; to SHIFT CTRL DOWN KEYquot; thread you posted? Was that one not resolved? Or is
gt; that a different quot;Danquot;?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Danquot; wrote:
gt;
gt; gt; Yes I did try the formula. Beginning at A1 if there is a value in A2 (which I
gt; gt; do not know if it will be filled) the formula became circular reference and
gt; gt; did not work. If I removed the data in A2 and inserted data in A4, the
gt; gt; formula did not count the blank spaces between A1 and A4 thereby returning 3.
gt; gt;
gt; gt; Regards,
gt; gt;
gt; gt; Dan
gt; gt;
gt; gt; quot;daddylonglegsquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Did you try the formula I posted?
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; daddylonglegs
gt; gt; gt; ------------------------------------------------------------------------
gt; gt; gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; gt; gt; View this thread: www.excelforum.com/showthread...hreadid=516708
gt; gt; gt;
gt; gt; gt;