software

How can I count the number of blank cells between
the row above and the last nonblank row above, if
the cell in this row is nonblank?

That is, the count is one or more only in the first
nonblank cell following one or more blank cells.

For example, suppose A1:A2 are nonblank, A3:A5
are blank, and A6:A9 are nonblank. I want B6 to
count 3 and B10 to count 4. All other cells in B:B
should be blank (or zero).

I know I can do this easily in VBA.

Is there some way to write a cell formula that only
uses built-in Excel functions -- preferrably not even
add-ins?


testing column A, and using column C as a helper column, in C2 put

=IF(A2lt;gt;quot;quot;,quot;quot;,IF(C1=quot;quot;,1,C1 1))

and in B2 put

=IF(A3=quot;quot;,quot;quot;,C2)

and formula drag B2 and C2 down to cover your data.

--
Wrote:
gt; How can I count the number of blank cells between
gt; the row above and the last nonblank row above, if
gt; the cell in this row is nonblank?
gt;
gt; That is, the count is one or more only in the first
gt; nonblank cell following one or more blank cells.
gt;
gt; For example, suppose A1:A2 are nonblank, A3:A5
gt; are blank, and A6:A9 are nonblank. I want B6 to
gt; count 3 and B10 to count 4. All other cells in B:B
gt; should be blank (or zero).
gt;
gt; I know I can do this easily in VBA.
gt;
gt; Is there some way to write a cell formula that only
gt; uses built-in Excel functions -- preferrably not even
gt; add-ins?--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=533202Bryan Hessey wrote:
gt; testing column A, and using column C as a helper column,
gt; in C2 put
gt; =IF(A2lt;gt;quot;quot;,quot;quot;,IF(C1=quot;quot;,1,C1 1))
gt; and in B2 put
gt; =IF(A3=quot;quot;,quot;quot;,C2)
gt; and formula drag B2 and C2 down to cover your data.

Well, I think I screwed up my example, but I got the idea.
Works great! Thanks.
Good to see it works for you, and thanks for the response

--
Wrote:
gt; Bryan Hessey wrote:
gt; gt; testing column A, and using column C as a helper column,
gt; gt; in C2 put
gt; gt; =IF(A2lt;gt;quot;quot;,quot;quot;,IF(C1=quot;quot;,1,C1 1))
gt; gt; and in B2 put
gt; gt; =IF(A3=quot;quot;,quot;quot;,C2)
gt; gt; and formula drag B2 and C2 down to cover your data.
gt;
gt; Well, I think I screwed up my example, but I got the idea.
gt; Works great! Thanks.--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=533202