Error proof Average?

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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.
 
<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
 
Upvote 0
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.
 
Upvote 0
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:
Rich (BB code):
=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)))))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
Rich (BB code):
=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)))))
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top