Dan
I didn't realize you only want to count the number of blank cells.
See if these alternatives give you something you can work with:
Note: Commit both of these array formula by holding down the [Ctrl][Shift]
keys and press [Enter].
Option 1:
B1: =MIN(NOT(ISBLANK(A2:A100))*ROW(A2:A100)+ISBLANK(A2:A100)*10^10)-ROW(A1)-1
Copy/paste that formula from B2 down as far as you need.
The formula returns the number of blank cells between the referenced cell
and the next non-blank cell below it.
Option 2:
C1: (a cell address from Col_A: Example: A2)
B1:
=MIN(NOT(ISBLANK(OFFSET(INDIRECT(C1),1,0):A100))*ROW(OFFSET(INDIRECT(C1),1,0):A100)+ISBLANK(OFFSET(INDIRECT(C1),1,0):A100)*10^10)-ROW(INDIRECT(C1))-1
Changing the cell address in C1 will cause the formula to return the number
of blank cells between that reference and the next non-blank cell below it.
Note: if a formula contains =" ", it is NOT blank.
Change the references to suit your situation. Right now the formulas only
look down limited number of rows (up to 100), but you can easily change that.
Any closer this time?
***********
Regards,
Ron
XL2002, WinXP-Pro
Dan said:
Didn't work. The formula only counts the number of non blank cells from A1
and not the blank cells between them.
A little more detail:
A1=1s2
A2=3d4
A3-A16=" "
A17=8u6
Axx=2n8
Ayy=9j6
The number of blanks betwen the first two cells is "0". Next is 13 (A2 to
A17), but what is the number of blanks between A17 and the next populated
cell in Column A where "xx" or "yy" is not known?
Thanks
Ron Coderre said:
Oops! I apologize..
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A25:A100))*10^10)-ROW(A25)
That's an array formula and must be commited by holding down the [Ctrl] and
[Shift] keys when you press [Enter].
***********
Regards,
Ron
XL2002, WinXP-Pro
:
Something is wrong with the formula. If I enter any value between A2 to A100
the answer is always "0". If I enter a value into A1 the answer is
"9999999999".
Trying to disect the equation to see if it works.
Thanks,
Dan
:
Yup...I understand, now.
See if this comes closer:
For a list of items (or blanks) in A1:A100, this formula returns the number
of contiguous non-blank cells, beginning with A1:
B1: =MIN(ISBLANK(A1:A100)*ROW(A1:A100)+NOT(ISBLANK(A1:A100))*10^10)-ROW(A1)
If you wanted to start from A25, this is the form:
B1:
=MIN(ISBLANK(A25:A100)*ROW(A25:A100)+NOT(ISBLANK(A25:A100))*10^10)-ROW(A25)
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
:
It would if I knew the end cell, i.e. "C10", but I do not. I have no idea
which row the information will be entered. The only cell I know is the
starting cell, i.e. A3. The next cell could be A5 or A16.
Thanks
:
Try something like this:
=ROWS(A1:C10)
In that case, the function returns 10.
Does that help?
***********
Regards,
Ron
XL2002, WinXP-Pro
:
Is there a function that will return the number of rows between two cells of
unknown distance, similar to selecting the first cell and pressing
SHIFT+CRTL+DOWN KEY and the number of rows is displayed in the "Name Box" in
the upper left corner of the spread sheet?
Thanks
Dan
