=SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw
Data'!$I$2:$I5000)<0,AVERAGE('Raw Data'!$I$2: $I5000),"")

The ranges have to be the same size.

Try this array formula** :

=AVERAGE(IF(YEAR('Raw Data'!C2:C5000)=2008,'Raw Data'!I2: I5000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"Danny Boy" wrote in message
...
I am trying to get this formula to average the values in row I for all
individuals seen during the year 2008. The formula also acknowledges that
it
should ignore any cell in row I, if it is blank. For some reason I can't
seem
to get the formula to work, and I keep getting a #VALUE! error. Any
suggestions?

Thanks,

Dan

Below is the formula I originally wrote:
=SUMPRODUCT((--(YEAR('Raw Data'!$C$2:$C$3498)=2008)),('Raw
Data'!$I$2:$I5000)<0,AVERAGE('Raw Data'!$I$2: $I5000),"")