http://ocotal.iarc.uaf.edu/api.php?action=feedcontributions&feedformat=atom&user=137.229.29.95&* 2026-05-12T22:06:52Z User contributions MediaWiki 1.34.2 http://ocotal.iarc.uaf.edu/index.php?title=Monthly_Averages&diff=1640 2011-11-28T20:47:27Z

<p>137.229.29.95: </p> <hr /> <div>Use this program to compute monthly averages.<br /> &lt;pre&gt;<br /> Option Explicit<br /> Public Sub monthly_avg()<br /> Dim datecol As Integer<br /> Dim inputcol As Integer<br /> Dim outcol As Integer<br /> Dim worksheetz As Integer<br /> Dim startrow As Long<br /> Dim outputdate As Boolean<br /> Dim outputcount As Boolean<br /> Dim columnincr As Integer<br /> <br /> ' stuff that should be set just initially<br /> datecol = 1<br /> worksheetz = 3<br /> startrow = 4<br /> outputcount = True<br /> outputdate = True<br /> inputcol = 2<br /> outcol = 30<br /> Call avg_column(datecol, inputcol, outcol, worksheetz, startrow, outputdate, outputcount)<br /> <br /> End Sub<br /> <br /> Public Sub avg_column(datecol As Integer, inputcol As Integer, outcol As Integer, worksheetz As Integer, startrow As Long, outputdate As Boolean, outputcount As Boolean)<br /> Dim row As Long<br /> Dim count As Integer<br /> Dim themonth As Integer<br /> Dim curdata As Double<br /> Dim monthsum As Double<br /> Dim outrow As Integer<br /> <br /> outrow = startrow<br /> row = startrow<br /> <br /> themonth = Int(Format(Worksheets(worksheetz).Cells(row, datecol).Value, &quot;MM&quot;))<br /> curdata = Worksheets(worksheetz).Cells(row, inputcol).Value<br /> If Abs(curdata) &lt; 6999 Then<br /> monthsum = curdata<br /> count = 1<br /> End If<br /> row = row + 1<br /> Do While Worksheets(worksheetz).Cells(row, datecol).Value &lt;&gt; &quot;&quot;<br /> If themonth = Int(Format(Worksheets(worksheetz).Cells(row, datecol).Value, &quot;MM&quot;)) Then<br /> ' same day<br /> curdata = Worksheets(worksheetz).Cells(row, inputcol).Value<br /> If Abs(curdata) &lt; 6999 Then<br /> monthsum = monthsum + curdata<br /> count = count + 1<br /> End If<br /> Else:<br /> ' new day, do some outputting<br /> If outputdate = True Then<br /> ' output the day<br /> Worksheets(worksheetz).Cells(outrow, outcol).Value = themonth<br /> If count &gt; 0 Then<br /> ' output daily average<br /> Worksheets(worksheetz).Cells(outrow, outcol + 1).Value = monthsum / count<br /> End If<br /> If outputcount = True Then<br /> ' output the count if the boolean is true<br /> Worksheets(worksheetz).Cells(outrow, outcol + 2).Value = count<br /> End If<br /> Else:<br /> ' don't output the day<br /> If count &gt; 0 Then<br /> ' output daily average<br /> Worksheets(worksheetz).Cells(outrow, outcol).Value = monthsum / count<br /> End If<br /> If outputcount = True Then<br /> ' output the count if the boolean is true<br /> Worksheets(worksheetz).Cells(outrow, outcol + 1).Value = count<br /> End If<br /> End If<br /> outrow = outrow + 1<br /> ' okay, done outputting. Now to ingest the new data as before.<br /> ' first reset the vars.<br /> count = 0<br /> monthsum = 0<br /> themonth = Int(Format(Worksheets(worksheetz).Cells(row, datecol).Value, &quot;MM&quot;))<br /> curdata = Worksheets(worksheetz).Cells(row, inputcol).Value<br /> If Abs(curdata) &lt; 6999 Then<br /> monthsum = curdata<br /> count = 1<br /> End If<br /> <br /> End If<br /> row = row + 1<br /> Loop<br /> ' drop in the stuff for the last day of year.<br /> ' new day, do some outputting<br /> If outputdate = True Then<br /> ' output the day<br /> Worksheets(worksheetz).Cells(outrow, outcol).Value = themonth<br /> If count &gt; 0 Then<br /> ' output daily average<br /> Worksheets(worksheetz).Cells(outrow, outcol + 1).Value = monthsum / count<br /> End If<br /> If outputcount = True Then<br /> ' output the count if the boolean is true<br /> Worksheets(worksheetz).Cells(outrow, outcol + 2).Value = count<br /> End If<br /> Else:<br /> ' don't output the day<br /> If count &gt; 0 Then<br /> ' output daily average<br /> Worksheets(worksheetz).Cells(outrow, outcol).Value = monthsum / count<br /> End If<br /> If outputcount = True Then<br /> ' output the count if the boolean is true<br /> Worksheets(worksheetz).Cells(outrow, outcol + 1).Value = count<br /> End If<br /> End If<br /> End Sub<br /> &lt;/pre&gt;</div>

137.229.29.95 http://ocotal.iarc.uaf.edu/index.php?title=Excel_Macros&diff=467 2011-11-28T20:39:41Z

<p>137.229.29.95: /* Handy Code Snippets */</p> <hr /> <div>== Handy Code Snippets ==<br /> This page contains links to several Excel Macros. Feel free to use or add additional ones.<br /> * [[Save Macros in Excel]]-- a brief How-To<br /> * [[Tool Bar Button|Create a Toolbar button]] -- a brief How-To (useful with some macros)<br /> * [[Julian to regular date converter]] <br /> * [[Regular Date to Julian Converter]]<br /> * [[Calculate Daily Averages / Sums from hourly data| MicroMet Format Daily Averages]]<br /> * [[Daily Averages 2| General Daily average Macro]]<br /> * [[Monthly Averages| General Monthly average Macro]]<br /> * [[DewPoint Calculator Routine]]<br /> * [[Double Line Delete| Line Deleter for going through online data from shand]]<br /> * [[Cell Wrap]] -- Useful for long column headers in data spreadsheets.<br /> * [[Data Split]] -- Short Macro for splitting comma &amp; tab separated data such as when you open a .txt file in Excel<br /> * [[LI7700 Data Parser]] -- Short Macro for junking bad data on a quick first cut for a LI-7700 Open Path CH4 Analyzer</div>

137.229.29.95