You may be better changing your
=IF(OR(ISBLANK(DL21),ISBLANK(AF21)),"",DL21/AF21)
to
=IF(OR(DL21="",AF21=""),"",DL21/AF21)

ISBLANK will not be true for cells containing a formula that results in "".
--
David Biddulph

"Andrew" wrote in message
...
One of the problems I have is that for some of the cells in AZ(or other
moving data columns) return #VALUE, which means that the formula which
calls
for those cells also returns #VALUE. Any global settings in Excell that
let
one handle missing data efficiently? It is getting tedious to be
constantly
entering formulas to make blanks. for example:
=IF(OR(ISBLANK(DL21),ISBLANK(AF21)),"",DL21/AF21)

"Andrew" wrote:

Elkthar formula is very close, and does copy across cells now , however
there
are some slight discrepancies in the mean values it returns. Also, for
many
columns it returns VALUE error. I think the two arrays are not always
the
same size, and thus it can't multiply those arrays.
=SUMPRODUCT(--(Main!$C$2:$C$266=$C8),Main!AZ$2:AZ$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C8),--(Main!AZ$2:AZ$266<""))

Ashish, how would I apply the condition if not in the criteria of
DAVERAGE?
"Ashish Mathur" wrote:

Hi,

There is some problem in the criteria of your formula. The criteria
has to
be a range of cells and not an equation such as what you have used
(Main!$C$2:$C$266=$C11). Also please ensure that the headers of the
range
and criteria are the same.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Andrew" wrote in message
...
I am trying to reduce a large dataset of 266 individuals of 23
species
into
one that just includes the means of each species. Species names are
text
entries in column C. There are some missing data points, so I could
not
simply use:
=SUMIF(Main!$C$2:$C$266,$C11,Main!FF$2:FF$266)/COUNTIF(Main!$C$2:$C$266,$C11)
because the countif is not counting the exact cells going into the
sum.

So my current approach is to use DAVERAGE. I am specifying the
worksheet(Database?), column, and criteria. The criteria compares
text in
the 2 columns

Daverage(Main!1:65536,FF$2:FF$266,Main!$C$2:$C$266 =$C11)
however, I am getting a #VALUE error.
any help, thanks