Weekly and Monthly Averages from Daily Data

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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.
 
Sort by date Sort by votes
So what I need is to find an alternative or expand the proposed formula that appears to work (see below) to ignore errors in the numerator caused by the data column and if the array in the denominator is zero to just place a blank in the cell, otherwise the formula itself will produce the division by zerio error.

Code: 
=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))
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
510
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
923
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
504
josros60
J

Forum statistics

Threads
1,217,383
Messages
6,136,244
Members
450,001
Latest member
KWeekley08

Share this page

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