Weekly and Monthly Averages from Daily Data

[dwgnome]

I am trying to come up with formulas that will list the weekly dates based on the first date in the data table and compute corresponding weekly average. In this case, the first week must be the first full week in that month that starts on a Sunday. All other weeks must start on the subsequent Sunday. The first full week of January 2011 starts on the 2nd. So the weekly average should be from Jan 2 thorugh and including Jan 8. And for the last week in January, starting on the 30th, the average should be representative of data from Jan 30 through February 5. So the weeks will run into the following month. Since the year chosen for the averages could change, I need the formula to be "smart" enough to compute the first full week of any given year.

For the monthly averages, those should be for calendar months (ie., Jan 1 thorugh Jan 31). I need both weekly and monthly averages to ignore blanks and any text or Excel formula errors. Also, the weekly, monthly and annual averages should be dependent on the user picked year (cell D733).

I will likely put these averages on another worksheet and let the daily data run from A4:A4000 covering multiple years.

Daily NG Btu

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 89px"><COL style="WIDTH: 73px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 67px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Btu</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">733</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/1/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.80</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">734</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/2/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.80</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">735</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/3/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.40</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">736</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/4/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.30</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">737</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/5/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,015.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">738</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/6/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,016.30</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">739</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/7/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,016.10</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">740</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/8/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.80</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">741</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/9/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.60</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">742</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/10/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.50</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">743</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/11/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,019.40</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">744</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/12/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.60</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">745</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/13/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.30</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">746</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/14/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,016.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">747</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/15/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.50</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">748</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/16/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.50</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">749</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/17/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,016.30</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">750</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/18/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,015.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">751</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/19/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,016.60</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">752</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/20/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.60</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">753</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/21/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">754</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/22/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">755</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/23/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,013.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">756</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/24/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.30</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">757</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/25/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.40</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">758</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/26/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">759</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/27/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.80</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">760</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/28/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.60</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">761</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/29/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.40</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">762</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/30/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.40</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">763</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1/31/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,014.10</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">764</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/1/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,016.10</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">765</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/2/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,019.50</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">766</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/3/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,019.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">767</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/4/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,020.40</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">768</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/5/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,020.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">769</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/6/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,022.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">770</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/7/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,021.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">771</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/8/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.80</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">772</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/9/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,019.90</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">773</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/10/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,021.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">774</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/11/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,020.20</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">775</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/12/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,020.00</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">776</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/13/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.70</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">777</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/14/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.50</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">778</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/15/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,018.80</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">779</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">2/16/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1,017.40</TD></TR></TBODY></TABLE>

Daily NG Btu

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 94px"><COL style="WIDTH: 94px"><COL style="WIDTH: 103px"><COL style="WIDTH: 95px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 67px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Year</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Week</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Weekly Average</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Month</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Monthly Average</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ccffcc; FONT-WEIGHT: bold">Annual Average</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">733</TD><TD style="TEXT-ALIGN: center; COLOR: #ff0000; FONT-WEIGHT: bold">2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1/2/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1016.10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1/1/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1017.02</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">1013.149</TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">734</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1/9/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1018.11</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">2/1/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1016.78</TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">735</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1/16/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1017.13</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">3/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">736</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1/23/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1016.19</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">4/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">737</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1/30/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1017.90</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">5/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">738</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">2/6/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1020.41</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">6/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">739</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">2/13/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1016.73</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">7/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">740</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">2/20/2011</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">1013.34</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">8/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">741</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">2/27/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">9/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">742</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">3/6/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">10/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">743</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">3/13/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">11/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR><TR style="HEIGHT: 20px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">744</TD><TD></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">3/20/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana">12/1/2011</TD><TD style="FONT-FAMILY: Verdana"></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F733</TD><TD>=AVERAGE(B734:B740)</TD></TR><TR><TD>H733</TD><TD>=AVERAGE(B733:B768)</TD></TR><TR><TD>I733</TD><TD>=SUMIF($B$733:$B$1097,">0")/COUNTIF($B$733:$B$1097,">0")</TD></TR><TR><TD>E734</TD><TD>=E733+7</TD></TR><TR><TD>F734</TD><TD>=AVERAGE(B741:B747)</TD></TR><TR><TD>H734</TD><TD>=AVERAGE(B764:B791)</TD></TR><TR><TD>E735</TD><TD>=E734+7</TD></TR><TR><TD>E736</TD><TD>=E735+7</TD></TR><TR><TD>E737</TD><TD>=E736+7</TD></TR><TR><TD>E738</TD><TD>=E737+7</TD></TR><TR><TD>E739</TD><TD>=E738+7</TD></TR><TR><TD>E740</TD><TD>=E739+7</TD></TR><TR><TD>E741</TD><TD>=E740+7</TD></TR><TR><TD>E742</TD><TD>=E741+7</TD></TR><TR><TD>E743</TD><TD>=E742+7</TD></TR><TR><TD>E744</TD><TD>=E743+7</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Any assistance is appreciated.

 

[RonB1111]

Easiest way is with a pivot table with grouping by day (7 days) and month. You can set the start date in the grouping to 1/2/11. Is this an acceptable option for you?

 

[dwgnome]

Thank you for your reply. I prefer the formula method.

 

[RonB1111]

Averages are in a separate worksheet with headers in A1 thru F1. Data sheet is named "Daily". I noticed in an earlier post you said you use Excel 2003, so I used Sumproduct formula instead of averageifs formula.

In the averages sheet:
Enter year in A2
Enter 1st Sunday of year in B2 (eg: 1/2/11)
Formula in B3: =B2+7 and copy down
Formula in C2: =SUMPRODUCT(--(Daily!A$4:A$50>=B2),--(Daily!A$4:A$50<b2+7),(daily!b$4:b$50)) sumproduct(--(daily!a$4:a$50="">=B2),--(Daily!A$4:A$50<b2+7)) and="" copy="" down

Formula in D2: =DATE(A$2,IF(D1="Month",1,MONTH(D1)+1),1) and copy down
Formula in E2: =SUMPRODUCT(--(Daily!A$4:A$50>=D2),--(Daily!A$4:A$50<=EOMONTH(D2,0)),(Daily!B$4:B$50))/SUMPRODUCT(--(Daily!A$4:A$50>=D2),--(Daily!A$4:A$50<=EOMONTH(D2,0))) and copy down
Formula in F2: =SUMPRODUCT(--(YEAR(Daily!A4:A50)=A2),(Daily!B4:B50))/SUMPRODUCT(--(YEAR(Daily!A4:A50)=A2))</b2+7))></b2+7),(daily!b$4:b$50))>

 

[dwgnome]

Thanks for the suggestion. However, I cannot seem to get past the SUMPRODUCT formula in C2. The formula appears to be missing parenthesis. When I added them, the result was zero, not 1016.0 as expected.

If you or someone can just put the formulas on the same page as I presented so that I can follow how the different formulas are working, that would help.

So I think the first SUMPRODUCT formula should be in F733 and so on.

 

[RonB1111]

You're right, here's the complete formula for C2:
=SUMPRODUCT(--(Daily!A$4:A$50>=B2),--(Daily!A$4:A$50<B2+7),(Daily!B$4:B$50))/SUMPRODUCT(--(Daily!A$4:A$50>=B2),--(Daily!A$4:A$50<B2+7))

 

[RonB1111]

Sorry, I'll try again (please remove the spaces in this formula before entering in your spreadsheet. For cell C2 and copy down:
=SUMPRODUCT(--(Daily!A$4:A$50 > =B2),--(Daily!A$4:A$50 < B2+7),(Daily!B$4:B$50))/SUMPRODUCT(--(Daily!A$4:A$50 > =B2),--(Daily!A$4:A$50 < B2+7))

 

[dwgnome]

Thanks for correcting. All the formulas you suggested worked and for the limited sample I provided. The only thing I did different is to use =DATE(A$5,ROWS(D$5:D5),1) for creating the months.

However, what I actually need is a way to have the weeks be dependant on the year chosen and to have a formula pull the date of the first full week starting on Sunday.

Also, if there are errors in the data column such as #VALUE!, #NAME? or #DIV/0!, the averages should ignore instead of mirror the error.

For the annual average I can use:

=SUMIF($B$733:$B$1097,">0")/COUNTIF($B$733:$B$1097,">0")

This then ignores any error, text and blanks. So I was wondering if there is a way to adapt that to the weekly and monthly averaging scheme.

Can you help me on that?

Although you figured it out, I thought I would just clarify for everyone that I use Excel 2003.

 

[RonB1111]

Enter this formula in B2 to calculate the 1st sunday of the year in A2:
=DATE(A2,1,LOOKUP(WEEKDAY(DATE(A2,1,1)),{1,2,3,4,5,6,7;1,7,6,5,4,3,2}))

Your proposed Annual Average formula won't be based on the selected year in A2 - you said in your 1st post you'd have a separate sheet for the daily data so it could run for multiple years. Also, why wouldn't you fix the formulas/data in the daily data that created the error condition rather than ignoring them in your average calcs and allowing the averages to be incorrect?

 

[dwgnome]

Yes, thanks, that did it for the start of the weekly dates. Thank you. I even enhanced the fill down to stay only within the year specified if I copy down too far using in B6:

=IF(LEN(B5)<1,"",IF(AND(ISNUMBER(B5),B5+7<=DATE($A$5,12,31)),B5+7,""))

I cannot change the formulas in the data column, so if there is #DIV/0!, then I do not want that to be echoed in the result as SUMPRODUCT cannot seem to handle arrays with errors. I even tried NOT(ISERROR(... to get SUMPRODUCT to ignore any errors but still did not work. So my original request still stands on handling text and errors.