Hi Bob,
your formula: =AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6))

returns 0, when I am expecting a nonzero return.


Elkar's formula works, but only in that one cell.
-Andrew

"Bob Phillips" wrote:

Try this array formula

=AVERAGE(IF(Main!$C$2:$C$266=C11,Main!$FF$2:$FF$26 6))

--
__________________________________
HTH

Bob

"Andrew" wrote in message
...
That does seem to work locally, but I can't spread it across the
spreadsheet
without getting a VALUE error. I then tried removing the dollars signs
from
the moving cells FF, but have not quite got it yet.

"Elkar" wrote:

How about something like:

=SUMPRODUCT(--(Main!$C$2:$C$266=$C11),Main!$FF$2:$FF$266)/SUMPRODUCT(--(Main!$C$2:$C$266=$C11),--(Main!$FF$2:$FF$266<""))

HTH
Elkar


"Andrew" wrote:

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