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.