Counting blank cells

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?

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.
--
Kevin Backmann


"geoff1234" wrote:

>
> 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?
>
>
> --
> geoff1234
> ------------------------------------------------------------------------
> geoff1234's Profile: http://www.excelforum.com/member.php...o&userid=36051
> View this thread: http://www.excelforum.com/showthread...hreadid=559397
>
>

Try...

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

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

Hope this helps!

In article <[email protected]>,
geoff1234 <[email protected]>
wrote:

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

Hey Domenic,

What's your take on 99^99 ?

Any opinions or foreseeable problems?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Try...
>
> =COUNTBLANK(INDEX(A2:A10,MATCH(BigNum,A2:A10)+1):A10)
>
> ...where BigNum is defined as 9.99999999999999E+307.
>
> Hope this helps!
>
> In article <[email protected]>,
> geoff1234 <[email protected]>
> wrote:
>
>> 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?

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!

In article <[email protected]>,
"Ragdyer" <[email protected]> wrote:

> Hey Domenic,
>
> What's your take on 99^99 ?
>
> Any opinions or foreseeable problems?
> --
> Regards,
>
> RD

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

"Domenic" <[email protected]> wrote in message
news:[email protected]...
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!

In article <[email protected]>,
"Ragdyer" <[email protected]> wrote:

> Hey Domenic,
>
> What's your take on 99^99 ?
>
> Any opinions or foreseeable problems?
> --
> Regards,
>
> RD

In article <[email protected]>,
"RagDyeR" <[email protected]> wrote:

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

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!

thanks for all the help, got it working nicely now

this forum is awesome

peace out