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.

 

[dwgnome]

Respectfully a Bump.


Looked like there was a solution around the corner.

 

[Tinus Bergakker]

<table border="0" cellpadding="0" cellspacing="0" width="394"><col style="width: 52pt;" width="69" span="2"> <col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 52pt;" width="69" height="20">btu_week</td> <td style="width: 52pt;" width="69">btu_value</td> <td style="width: 48pt;" width="64">
</td> <td style="width: 48pt;" width="64">
</td> <td colspan="2" style="width: 96pt;" width="128">Monthly Ave</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/3/2010</td> <td>
</td> <td>
</td> <td>
</td> <td>January</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/10/2010</td> <td align="right">500</td> <td align="right">500</td> <td>
</td> <td>February</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/17/2010</td> <td align="center">#NAME?</td> <td>
</td> <td>
</td> <td>March</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/24/2010</td> <td align="right">400</td> <td align="right">400</td> <td>
</td> <td>April</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/31/2010</td> <td align="right">300</td> <td align="right">300</td> <td>
</td> <td>May</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">2/7/2010</td> <td align="right">800</td> <td align="right">800</td> <td>
</td> <td>June</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">2/14/2010</td> <td align="right">44</td> <td align="right">44</td> <td>
</td> <td>July</td> <td align="center">#NAME?</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">2/21/2010</td> <td align="right">444</td> <td align="right">444</td> <td>
</td> <td>August</td> <td align="center">#NAME?</td> </tr> </tbody></table>


Apply your formula on the column next to the one with the error. I used

=IF(OR(ISERROR(E5)=TRUE,ISBLANK(E5)),"",E5) which I dragged down

 

[dwgnome]

Yes, I see how that can work. It is essentially working around the errors using a helper column. However, do to other constraints, I cannot use helper columns, so I need to use the column with the errors as is. So I am looking for a solution that makes the formula in my original post more robust.

 

[Aladin Akyurek]

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

So the average for January should be 400.

Thanks for your assistance.

Assuming that you are on a system prior 2007...

<TABLE style="WIDTH: 335pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=447><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2588" width=73><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2872" width=81><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3043" width=86><TBODY><TR style="HEIGHT: 21.6pt; mso-height-source: userset" height=29><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc; WIDTH: 55pt; HEIGHT: 21.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=29 width=73>btu_week</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=79>btu_value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; WIDTH: 125pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=167 colSpan=2>Monthly Avg</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40181">1/3/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=79> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40179">January</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>400</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40188">1/10/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=right u1:num>500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40210" u1:fmla="=EDATE(E2,1)">February</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>429.3333333</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40195">1/17/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=middle u1:err="#NAME?">#NAME?</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40238" u1:fmla="=EDATE(E3,1)">March</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40202">1/24/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=right u1:num>400</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40269" u1:fmla="=EDATE(E4,1)">April</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40209">1/31/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=right u1:num>300</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40299" u1:fmla="=EDATE(E5,1)">May</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40216">2/7/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=right u1:num>800</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40330" u1:fmla="=EDATE(E6,1)">June</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40223">2/14/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=right u1:num>44</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40360" u1:fmla="=EDATE(E7,1)">July</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40230">2/21/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=79 align=right u1:num>444</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=81 u1:num="40391" u1:fmla="=EDATE(E8,1)">August</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=86 align=right>0</TD></TR></TBODY></TABLE>

Data is in A1:C9, including the in-between empty range and the headers.
Column E houses the criteria month names.

F2, control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
   AVERAGE(IF($A$2:$A$9-DAY($A$2:$A$9)+1=DATE(2010,MONTH("1-"&E2),1),
    IF(ISNUMBER($C$2:$C$9),$C$2:$C$9)))))

 

[dwgnome]

Thank you Aladin .

Using your solution I get zero after CSE. The dates column A are actual dates. The months are actually dates using EDATE function but formatted to show month. You have 2010 hard entered, is there a way for the formula to get the date/year from the "months" list and adjust the formula for this data type?

Thanks.

 

[Aladin Akyurek]

Thank you Aladin .

Using your solution I get zero after CSE. The dates column A are actual dates. The months are actually dates using EDATE function but formatted to show month. You have 2010 hard entered, is there a way for the formula to get the date/year from the "months" list and adjust the formula for this data type?

Thanks.

You have 1-Jan-2010, not just January in F2, right?

 

[dwgnome]

Correct. Actually more precisely 1/1/2010, then in F3 EDATE(F2,1) and so on.

 

[Aladin Akyurek]

Correct. Actually more precisely 1/1/2010, then in F3 EDATE(F2,1) and so on.

I meant to say E2... See the exhibit:

<TABLE style="WIDTH: 335pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=447><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2588" width=73><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2816" width=79><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2872" width=81><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3043" width=86><TBODY><TR style="HEIGHT: 21.6pt; mso-height-source: userset" height=29><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffffcc; WIDTH: 55pt; HEIGHT: 21.6pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=29 width=73>btu_week</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffffcc; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=79>btu_value</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; WIDTH: 125pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=167 colSpan=2>Monthly Avg</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40181">1/3/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=79> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40179">1-Jan-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>400</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40188">1/10/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=right u1:num>500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40210" u1:fmla="=EDATE(E2,1)">1-Feb-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>429.3333333</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40195">1/17/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=middle u1:err="#NAME?">#NAME?</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40238" u1:fmla="=EDATE(E3,1)">1-Mar-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40202">1/24/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=right u1:num>400</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40269" u1:fmla="=EDATE(E4,1)">1-Apr-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40209">1/31/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=right u1:num>300</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40299" u1:fmla="=EDATE(E5,1)">1-May-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40216">2/7/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=right u1:num>800</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40330" u1:fmla="=EDATE(E6,1)">1-Jun-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40223">2/14/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=right u1:num>44</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40360" u1:fmla="=EDATE(E7,1)">1-Jul-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=73 u1:num="40230">2/21/2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 59pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=79 align=right u1:num>444</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 61pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81 align=right u1:num="40391" u1:fmla="=EDATE(E8,1)">1-Aug-10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 width=86 align=right>0</TD></TR></TBODY></TABLE>

E2 is formatted to show 1-Jan-10. The actual entry is a date in a more familiar format (1/1/2010).

F2, control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
   AVERAGE(IF($A$2:$A$9-DAY($A$2:$A$9)+1=E2,
    IF(ISNUMBER($C$2:$C$9),$C$2:$C$9)))))

 

[dwgnome]

That worked perfectly. Your assistance is much appreciated.


I know your are very busy helping everyone else, just curious, is there actually a way to get my original formula to work the same as your wonderful solution. I guess I'm just so much more comfortable with SUMPRODUCT construct. Not sure why the LOOKUP and CHOOSE combination are able to circumvent the error messages while my SUMPRODUCT with ISNUMBER function doesn't work.

 

[Aladin Akyurek]

That worked perfectly. Your assistance is much appreciated.


I know your are very busy helping everyone else, just curious, is there actually a way to get my original formula to work the same as your wonderful solution. I guess I'm just so much more comfortable with SUMPRODUCT construct. Not sure why the LOOKUP and CHOOSE combination are able to circumvent the error messages while my SUMPRODUCT with ISNUMBER function doesn't work.

Invoking SumProduct for this task is not a good idea. Moreover, you need an IF-like tool to eliminate or filter out errors in the range to average.

Current formula does two things:

1) Average non-error numeric values;

2) Avoid yielding #DIV/0! errors.

For the first point, an IF-expression is included in the formula. The second point is done with LOOKUP and CHOOSE. The latter creates a two-value array like:

{0,400}

or

{0,#DIV/0!}

LOOKUP with the big number (an Excel constant) as look up value picks out always the last numeric value from arrays such as the foregoing exemples. Note that LOOKUP ignores error values...