http://ocotal.iarc.uaf.edu/index.php?action=history&feed=atom&title=Daily_Averages_2 2026-05-12T22:00:59Z Revision history for this page on the wiki MediaWiki 1.34.2 http://ocotal.iarc.uaf.edu/index.php?title=Daily_Averages_2&diff=738&oldid=prev 2008-08-01T20:30:44Z

<p></p> <p><b>New page</b></p><div>Copy and paste the text in the box into the visual basic editor that comes with Excel. To get access to personal.xls in the visual basic editor check out this article: [[Save Macros in Excel]]<br /> <br /> This macro and collection of subroutines creates daily averages from hourly data. It's a pretty general one. I used it originally with soil temperature strings where there are several columns of data that need to be changed from hourly to daily. So, there are a couple flags to assist with this. You can report the day if you'd like and also the number of samples in the 'daily' average for example. The first subroutine you can use to call the second one which does all the work for you.<br /> <br /> Any questions check with Bob. <br /> <br /> Option Explicit<br /> Public Sub daily_avg()<br /> Dim daycol As Integer<br /> Dim inputcol As Integer<br /> Dim outcol As Integer<br /> Dim worksheetz As Integer<br /> Dim startrow As Long<br /> Dim outputday As Boolean<br /> Dim outputcount As Boolean<br /> Dim columnincr As Integer<br /> <br /> ' stuff that should be set just initially<br /> daycol = 1<br /> worksheetz = 9<br /> startrow = 32<br /> outputcount = False<br /> outputday = True<br /> inputcol = 3<br /> outcol = 16<br /> Call avg_column(daycol, inputcol, outcol, worksheetz, startrow, outputday, outputcount)<br /> outputday = False<br /> For columnincr = 0 To 10<br /> inputcol = 4 + columnincr<br /> outcol = 18 + columnincr<br /> Call avg_column(daycol, inputcol, outcol, worksheetz, startrow, outputday, outputcount)<br /> Next<br /> End Sub<br /> <br /> Public Sub avg_column(daycol As Integer, inputcol As Integer, outcol As Integer, worksheetz As Integer, startrow As Long, outputday As Boolean, outputcount As Boolean)<br /> Dim row As Long<br /> Dim count As Integer<br /> Dim theday As Integer<br /> Dim curdata As Double<br /> Dim daysum As Double<br /> Dim outrow As Integer<br /> <br /> outrow = startrow<br /> row = startrow<br /> <br /> theday = Int(Worksheets(worksheetz).Cells(row, daycol).Value)<br /> curdata = Worksheets(worksheetz).Cells(row, inputcol).Value<br /> If Abs(curdata) &lt; 6999 Then<br /> daysum = curdata<br /> count = 1<br /> End If<br /> row = row + 1<br /> Do While Worksheets(worksheetz).Cells(row, daycol).Value &lt;&gt; &quot;&quot;<br /> If theday = Int(Worksheets(worksheetz).Cells(row, daycol).Value) Then<br /> ' same day<br /> curdata = Worksheets(worksheetz).Cells(row, inputcol).Value<br /> If Abs(curdata) &lt; 6999 Then<br /> daysum = daysum + curdata<br /> count = count + 1<br /> End If<br /> Else:<br /> ' new day, do some outputting<br /> If outputday = True Then<br /> ' output the day<br /> Worksheets(worksheetz).Cells(outrow, outcol).Value = theday<br /> If count &gt; 0 Then<br /> ' output daily average<br /> Worksheets(worksheetz).Cells(outrow, outcol + 1).Value = daysum / 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 = daysum / 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 /> daysum = 0<br /> theday = Int(Worksheets(worksheetz).Cells(row, daycol).Value)<br /> curdata = Worksheets(worksheetz).Cells(row, inputcol).Value<br /> If Abs(curdata) &lt; 6999 Then<br /> daysum = 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 outputday = True Then<br /> ' output the day<br /> Worksheets(worksheetz).Cells(outrow, outcol).Value = theday<br /> If count &gt; 0 Then<br /> ' output daily average<br /> Worksheets(worksheetz).Cells(outrow, outcol + 1).Value = daysum / 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 = daysum / 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</div>

137.229.71.165