COUNTBLANK function

On Sun, 1 Jan 2006 11:33:39 -0500, "Mike" <windme@cox.net> wrote:

>Happy New Year Everyone,
>
>Using Excel XP.
>
>I have 3 columns of data, A1:C10. I want to count the blanks in C1:C10 ONLY
>if there is data in A1:A10.
>
>Example:
>
> A B C
>----------------------------------------------
> 1 20 6 5
> 2 4 1
> 3 8 6 2
> 4 4 2
> 5 8 1 1
> 6 7 2
> 7 3 2 2
> 8
> 9
> 10
>
>In the above example I need a formula that counts the blanks in C1:C10 but
>only if there is data in A1:A10, so that I get a total of
>3 (C2,C4 & C6 are blank). When I use the countblank function it counts
>all the blanks in C1:C10 (6).
>
>Thanks in advance,
>Mike
>
>

Something like:

=SUMPRODUCT(NOT(ISBLANK(A1:A10))*ISBLANK(C1:C10))

One caveat -- if cell_ref contains a formula, then =ISBLANK(cell_ref)-->FALSE.

If the data in column A is the result of a formula, you may need to test for
whatever it is that the formula is returning to look like an empty cell; or you
could test for a number being present in the cell, if number is the only valid
entry.


--ron

Ron Rosenfeld wrote:

>
> Something like:
>
> =SUMPRODUCT(NOT(ISBLANK(A1:A10))*ISBLANK(C1:C10))
>
> One caveat -- if cell_ref contains a formula, then =ISBLANK(cell_ref)-->FALSE.
>
> If the data in column A is the result of a formula, you may need to test for
> whatever it is that the formula is returning to look like an empty cell; or you
> could test for a number being present in the cell, if number is the only valid
> entry.
>
>
> --ron

That may answer a question I was going to ask. I have a spreadsheed I'm
trying to use for daily and weekly averages.

Column C is a morning value, column D is an evening value and column E
is the daily average, column F has a weekly average.

Because of the data collection, there may be one or many values missing
during a week. I need to average what is there. In the meantime, until
there's data, I want the cells where I am averaging to remain blank.

I'm trying to make it look neat by having it maintain blank cells in
columns E & F until data is input into one of the cells in column C or D.

This formula works in Cell E9:

=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(D9))),AVERAGE(C9:D9),"")

It also works in E10, E11 ... E375 with the appropriate values for the row.

I know it's a kludge, but it does give me a blank cell until there's
data in cells C9 or D9.

For the weekly average, I tried the following in Cell F15:

=IF(OR(NOT(ISBLANK(E9)),NOT(ISBLANK(E10))),AVERAGE(E9:E15),"")

It gives me a #DIV/0 error until there's some data in cell C9
(or in D9, C10 or D10).

But if I directly test cell C9 or C10 it does work:

=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(C10))),AVERAGE(E9:E15),"")

However, what I need is a generic test for the whole week's data, that
there's a value somewhere in C9:D15

I'll give the SUMPRODUCT function a try, but I'm open to suggestions.

On Sun, 01 Jan 2006 17:24:01 GMT, no_name <no_name@no.where.invalid> wrote:

>Ron Rosenfeld wrote:
>
>>
>> Something like:
>>
>> =SUMPRODUCT(NOT(ISBLANK(A1:A10))*ISBLANK(C1:C10))
>>
>> One caveat -- if cell_ref contains a formula, then =ISBLANK(cell_ref)-->FALSE.
>>
>> If the data in column A is the result of a formula, you may need to test for
>> whatever it is that the formula is returning to look like an empty cell; or you
>> could test for a number being present in the cell, if number is the only valid
>> entry.
>>
>>
>> --ron
>
>That may answer a question I was going to ask. I have a spreadsheed I'm
>trying to use for daily and weekly averages.
>
>Column C is a morning value, column D is an evening value and column E
>is the daily average, column F has a weekly average.
>
>Because of the data collection, there may be one or many values missing
>during a week. I need to average what is there. In the meantime, until
>there's data, I want the cells where I am averaging to remain blank.
>
>I'm trying to make it look neat by having it maintain blank cells in
>columns E & F until data is input into one of the cells in column C or D.
>
>This formula works in Cell E9:
>
>=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(D9))),AVERAGE(C9:D9),"")

This might be a little simpler:

=IF(COUNT(C9:D9)=0,"",AVERAGE(C9:D9))

>
>It also works in E10, E11 ... E375 with the appropriate values for the row.
>
>I know it's a kludge, but it does give me a blank cell until there's
>data in cells C9 or D9.
>
>For the weekly average, I tried the following in Cell F15:
>
>=IF(OR(NOT(ISBLANK(E9)),NOT(ISBLANK(E10))),AVERAGE(E9:E15),"")
>
>It gives me a #DIV/0 error until there's some data in cell C9
>(or in D9, C10 or D10).
>
>But if I directly test cell C9 or C10 it does work:
>
>=IF(OR(NOT(ISBLANK(C9)),NOT(ISBLANK(C10))),AVERAGE(E9:E15),"")
>
>However, what I need is a generic test for the whole week's data, that
>there's a value somewhere in C9:D15

Actually, all you have to test for is E9:E15; and you can do it the same as
above:

=IF(COUNT(E9:E15)=0,"",AVERAGE(E9:E15))




>
>I'll give the SUMPRODUCT function a try, but I'm open to suggestions.

--ron

Ron Rosenfeld wrote:

> On Sun, 01 Jan 2006 17:24:01 GMT, no_name <no_name@no.where.invalid> wrote:
>
>
>>Ron Rosenfeld wrote:
>>
>>
>>>Something like:
>>>
>>>=SUMPRODUCT(NOT(ISBLANK(A1:A10))*ISBLANK(C1:C10))
>>>
>>>One caveat -- if cell_ref contains a formula, then =ISBLANK(cell_ref)-->FALSE.
>>>

>>Because of the data collection, there may be one or many values missing
>>during a week. I need to average what is there. In the meantime, until
>>there's data, I want the cells where I am averaging to remain blank.
>>
>>I'm trying to make it look neat by having it maintain blank cells in
>>columns E & F until data is input into one of the cells in column C or D.


>>
>>However, what I need is a generic test for the whole week's data, that
>>there's a value somewhere in C9:D15
>
>
> Actually, all you have to test for is E9:E15; and you can do it the same as
> above:
>
> =IF(COUNT(E9:E15)=0,"",AVERAGE(E9:E15))

Thanks.

I had done a similar sheet before and I knew the solution was a lot
simpler than all that nested IF(NOT(OR))) kludge.

I just couldn't remember it or figure it out again.