Formula problems that keep producing a #Value! Error!!
=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),"")
|