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