Calculating totals but eliminating month not complete

[jeffreybrown]

Hi All,

Since we are still in March therefore not complete, how can I keep the $103 for March from showing up in the average. Is it possible to do the same for the total in I2, not total up March until complete. Lastly, the formula in I15 works fine, but is there another way of writing this formula with Average?

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 69px"><COL style="WIDTH: 76px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Oct-08</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$520.60 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Nov-08</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$243.05 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Dec-08</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$273.40 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Jan-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$166.78 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Feb-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$249.15 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Mar-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$103.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Apr-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">May-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Jun-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Jul-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Aug-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Sep-09</TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$0.00 </TD><TD style="FONT-FAMILY: Calibri"> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-FAMILY: Calibri"> </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$1,555.98 </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Total to date</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-FAMILY: Calibri"> </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$259.33 </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Average</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="FONT-FAMILY: Calibri"> </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: right">$3,111.96 </TD><TD style="FONT-FAMILY: Calibri; TEXT-ALIGN: center">Projection</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>I2</TD><TD>=SUMPRODUCT(--(TEXT($B$3:$B$55,"mmmyyyy")=TEXT(H2,"mmmyyyy")),($E$3:$E$55))</TD></TR><TR><TD>I14</TD><TD>=SUM(I2:I13)</TD></TR><TR><TD>I15</TD><TD>=SUM(I2:I13)/COUNTIF(I2:I13,"<>0")</TD></TR><TR><TD>I16</TD><TD>=I15*COUNTA(H2:H13)</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

 

[barry houdini]

You could alter I2 like this

=IF(TODAY() >H2+31-DAY(H2+31),SUMPRODUCT(--(TEXT($B$3:$B$55,"mmmyyyy")=TEXT(H2,"mmmyyyy")),($E$3:$E$55)),"")

In that way you won't see the March total until 1st April, and all upcoming months will be blank so you can just use this formula for I15

=AVERAGE(A2:A13)

 

[mikerickson]

This uses a Named Value for clarity, but the formula for the name can be replaced into the spreadsheet formula if that style is prefered.

Name: LastOfPreviousMonth
RefersTo: =TODAY()-DAY(TODAY())
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=85><b>H</b><td align=center width=85><b>I</b><td align=center width=85><b>J</b><td align=center width=85><b>K</b>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Oct-08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$520.60 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Nov-08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$243.05 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Dec-08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$273.40 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Jan-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$166.78 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Feb-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$249.15 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Mar-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$103.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Apr-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000">May-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Jun-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>11</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Jul-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>12</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Aug-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>13</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Sep-09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$0.00 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>14</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$1,555.98 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Total to date</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">$1,452.98 </FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>15</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$259.33 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Average</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">$290.60 </FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>16</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">$3,111.96 </FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Projection</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">$3,487.15 </FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td><td align=center>K14 <td align = left >=SUMIF(H2:H13,"<"&LastOfPreviousMonth,I2:I13)</tr>
<tr><td><td align=center>K15 <td align = left >=K14/COUNTIF(H2:H13,"<"&LastOfPreviousMonth)</tr>
<tr><td><td align=center>K16 <td align = left >=K15*COUNTA($H$2:$H$13)</tr></table>

 

[jeffreybrown]

Thanks Mike & Barry...perfect as always