Error proof Average?

[dwgnome]

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.

 

[Tinus Bergakker]

This look like a formula you are applying so that it works with excels filter.

Is that right?

 

[dwgnome]

Actually not really. The formula is meant to isolate only the data that matches the month I am trying to average. The ISNUMBER function is in there to just make sure the values for both the dates and data are not text. Unfortunately, since the errors are a displayed feature not what is actually in the cell, the TEXT or ISNUMBER doesn't seem to catch that.

 

[Tinus Bergakker]

How about adding the condition:

IFERROR( your formula , "ERROR!") and modify

=SUMIF( $D$7:$D$58,"<>ERROR!" )

 

[dwgnome]

Sorry, I forgot to mention I am using Excel 2003. Not sure if ISERROR for Excel 2003 would be equivalent in setup.

 

[Tinus Bergakker]

To use IFERROR in excel 2003 use the following formulas

IF(ISERROR(formula) = TRUE, "ERROR!", formula)

 

[dwgnome]

I'll give it a try.

 

[dwgnome]

Your suggestion works. But it does not do what I need. I still want to average the valid data and ingore the displayed errors.

Also the =SUMIF( $D$7:$D$58,"<>ERROR!" ) did not work.

Any other ideas?

 

[Tinus Bergakker]

how about making using "" instead of "ERROR!" and then using just sum.

Excel automatically ignores blanks in its inbuilt formula

 

[dwgnome]

Again that just results in a blank when the formula encounters the error. What I am trying to accomplish is to average all data within a given month excluding the cells that have errors.

<TABLE style="WIDTH: 335pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=557 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=94><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=71><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=108><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1682" width=58><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=106><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=120><TBODY><TR style="HEIGHT: 18pt; mso-height-source: userset" height=30><TD class=xl22 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 56pt; BORDER-BOTTOM: #666699 2pt double; HEIGHT: 18pt; BACKGROUND-COLOR: #ffffcc" width=94 height=30>btu_week</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 43pt; BORDER-BOTTOM: #666699 2pt double; BACKGROUND-COLOR: #ffffcc" width=71></TD><TD class=xl24 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #666699 2pt double; BACKGROUND-COLOR: #ffffcc" width=108>btu_value</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 35pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=58></TD><TD class=xl35 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: navy 1pt solid; BORDER-LEFT: navy 1pt solid; WIDTH: 64pt; BORDER-BOTTOM: navy 1.5pt solid; BACKGROUND-COLOR: #ccffcc" align=middle width=106>Monthly Ave</TD><TD class=xl34 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: navy 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: navy 1.5pt solid; BACKGROUND-COLOR: #ccffcc" width=120></TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl26 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40181">1/3/2010</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl28 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl32 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40179">January</TD><TD class=xl36 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40188">1/10/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>500.0000</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40210" x:fmla="=EDATE(E2,1)">February</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40195">1/17/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40238" x:fmla="=EDATE(E3,1)">March</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40202">1/24/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>400.0000</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40269" x:fmla="=EDATE(E4,1)">April</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40209">1/31/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>300.0000</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40299" x:fmla="=EDATE(E5,1)">May</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40216">2/7/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>800.0000</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40330" x:fmla="=EDATE(E6,1)">June</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40223">2/14/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>44.0000</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40360" x:fmla="=EDATE(E7,1)">July</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR><TR style="HEIGHT: 15.95pt; mso-height-source: userset" height=26><TD class=xl29 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 15.95pt; BACKGROUND-COLOR: transparent" align=right height=26 x:num="40230">2/21/2010</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num>444.0000</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: navy 1pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="40391" x:fmla="=EDATE(E8,1)">August</TD><TD class=xl37 style="BORDER-RIGHT: navy 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:err="#NAME?">#NAME?</TD></TR></TBODY></TABLE>

So the average for January should be 400.

Thanks for your assistance.