Yes as year operates off dates whether in 'date' format or numerical format.
if you use 0 instead of NA it will assume they are 1900 which I would guess is wayyyy outside your dataset
Printable View
Yes as year operates off dates whether in 'date' format or numerical format.
if you use 0 instead of NA it will assume they are 1900 which I would guess is wayyyy outside your dataset
that appears to work. thanks guys.
Maybe
=IF(H2010<>"",
AVERAGE(IF(ISNUMBER(J2:J200),IF((YEAR(J2:J200)>=YEAR(H209))*(YEAR(J2:J200)< =YEAR(H10)),M2:M200))),
AVERAGE(IF(ISNUMBER(J2:J200),IF(YEAR(J2:J200)>=YEAR(H2009),M2:M200))))
and XLD takes it a step farther for the Win