Counting blank cells

G

geoff1234

Hi again,

I've got a list like this:

45
32
23
blank
34
45
blank
blank
blank

any idea how to count the number of blanks after the final non-blank
cell?
 
K

Kevin B

The formula below assumes the values are in Column A, rows 1 through 27, you
will need to adjust the range as necessary:

=COUNTIF(A1:A27,"")

The criteria following the comma in the formula is two quotation marks w/o a
space, not four consecutive apostrophes.
 
D

Domenic

Try...

=COUNTBLANK(INDEX(A2:A10,MATCH(BigNum,A2:A10)+1):A10)

....where BigNum is defined as 9.99999999999999E+307.

Hope this helps!
 
D

Domenic

Hi RagDyer!

As far as I can tell, I don't think it would be a problem in most cases.
But personally, I prefer to use 9.99999999999999E+307.

Since it's the largest number Excel recognizes, its use eliminates any
risk whatsoever regardless of how small or remote.

Cheers!
 
R

RagDyeR

But the 99^99 performs exactly the same function (no larger XL number), and
is easier to type.<bg>

Been using it for a while in some formulas at my plant, and am hoping that I
don't get bitten with any unforeseen future problems.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Hi RagDyer!

As far as I can tell, I don't think it would be a problem in most cases.
But personally, I prefer to use 9.99999999999999E+307.

Since it's the largest number Excel recognizes, its use eliminates any
risk whatsoever regardless of how small or remote.

Cheers!
 
D

Domenic

RagDyeR said:
But the 99^99 performs exactly the same function (no larger XL number), and
is easier to type.<bg>
Click to expand...

Sure! In practice, it's not likely that data will contain a number
larger than 99^99. However, since it's theoretically possible... <bg>

Cheers!
 
G

geoff1234

thanks for all the help, got it working nicely now

this forum is awesome

peace out
 
Top