[SOLVED] 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.

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))

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
>
>

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
>>
>>

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
> >>
> >>
>

Did you try the formula I posted?

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
>
>

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

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
> >
> >

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
> > >
> > >