Hi,
Trying to average column of numbers based on a monthly basis. The date column B is in ascending order, data to average is in column D. The formula I am using in G7 is:
=SUMPRODUCT(--(MONTH($B$7:$B$58)=MONTH(F7))*(ISNUMBER($B$7:$B$58)),--(ISNUMBER($D$7:$D$58)),$D$7:$D$58)/IF(SUMPRODUCT(--(MONTH($B$7:$B$58)=MONTH(F7))*(ISNUMBER($B$7:$B$58)),--(ISNUMBER($D$7:$D$58)))=0,1,SUMPRODUCT(--(MONTH($B$7:$B$58)=MONTH(F7))*(ISNUMBER($B$7:$B$58)),--(ISNUMBER($D$7:$D$58))))
Where F7 is 1/1/2010, F8 is 2/1/2010 etc.
I need the formula to ignore displayed errors (#NAME?, #VALUE! and #DIV/0!) within the data in column D.
=SUMIF( $D$7:$D$58,"<>#NAME?" ) works but I need for all errors and to be included somehow in my original formula above.
Thanks.
Trying to average column of numbers based on a monthly basis. The date column B is in ascending order, data to average is in column D. The formula I am using in G7 is:
=SUMPRODUCT(--(MONTH($B$7:$B$58)=MONTH(F7))*(ISNUMBER($B$7:$B$58)),--(ISNUMBER($D$7:$D$58)),$D$7:$D$58)/IF(SUMPRODUCT(--(MONTH($B$7:$B$58)=MONTH(F7))*(ISNUMBER($B$7:$B$58)),--(ISNUMBER($D$7:$D$58)))=0,1,SUMPRODUCT(--(MONTH($B$7:$B$58)=MONTH(F7))*(ISNUMBER($B$7:$B$58)),--(ISNUMBER($D$7:$D$58))))
Where F7 is 1/1/2010, F8 is 2/1/2010 etc.
I need the formula to ignore displayed errors (#NAME?, #VALUE! and #DIV/0!) within the data in column D.
=SUMIF( $D$7:$D$58,"<>#NAME?" ) works but I need for all errors and to be included somehow in my original formula above.
Thanks.