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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
633
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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