172.18.0.1: Created page with "

 Option Explicit Private Sub daily_avg_sum_etc()   ' worksheet variables   Dim inputrow As Long   Dim outputrow As Long   Dim inputWKS   Dim outputWKS   Dim dataCOL..."

2019-07-17T19:38:12Z

Created page with "<pre> Option Explicit Private Sub daily_avg_sum_etc() ' worksheet variables Dim inputrow As Long Dim outputrow As Long Dim inputWKS Dim outputWKS Dim dataCOL..."

New page

<pre>
Option Explicit
Private Sub daily_avg_sum_etc()
' worksheet variables
Dim inputrow As Long
Dim outputrow As Long
Dim inputWKS
Dim outputWKS
Dim dataCOL

' date and time variables
Dim inputdate
Dim olddate
Dim outputYEAR
Dim outputDAY
Dim outputMONTH
Dim outputTIME

' environmental sums for means
Dim countDATA As Double
Dim sumDATA As Double
Dim outputDATA As Double
Dim inputDATA As Double
Dim zoo As Double
Dim countELEVATED As Double
Dim sumELEVATED As Double
Dim outputELEVATED As Double
Dim outputPERCENT As Double
' environmental output variables

'''''''''''''''''''''''''''''''
' Start running through the data
'''''''''''''''''''''''''''''''
inputWKS = 1
inputrow = 5
outputWKS = 1
outputrow = 5
dataCOL = 2

olddate = Format(Worksheets(inputWKS).Cells(inputrow, 1).Value, "mm/dd/yyyy")

Do While Worksheets(inputWKS).Cells(inputrow, 1).Value <> Empty
inputdate = Format(Worksheets(inputWKS).Cells(inputrow, 1).Value, "mm/dd/yyyy")
If olddate <> inputdate Then
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' do final formatting and calculations prior to outputting
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
outputYEAR = Right(CStr(olddate), 4)
outputMONTH = Left(CStr(olddate), 2)
outputDAY = Mid(CStr(olddate), 4, 2)
outputTIME = 1
If countDATA <> 0 Then outputDATA = sensor_avg(sumDATA, countDATA) Else outputDATA = -9999
If countELEVATED <> 0 Then outputELEVATED = sensor_avg(sumELEVATED, countELEVATED) Else outputELEVATED = -9999
If countELEVATED <> 0 Then outputPERCENT = countELEVATED / countDATA * 100

''''''''''''''''''''''''''
' dump stuff to spreadsheet
''''''''''''''''''''''''''
Worksheets(outputWKS).Cells(outputrow, 4).Value = outputYEAR
Worksheets(outputWKS).Cells(outputrow, 5).Value = outputMONTH
Worksheets(outputWKS).Cells(outputrow, 6).Value = outputDAY
Worksheets(outputWKS).Cells(outputrow, 7).Value = outputDATA
Worksheets(outputWKS).Cells(outputrow, 8).Value = countDATA
Worksheets(outputWKS).Cells(outputrow, 9).Value = outputELEVATED
Worksheets(outputWKS).Cells(outputrow, 10).Value = outputPERCENT

'''''''''''''''''''''''''''''''
' reset values and increment row
'''''''''''''''''''''''''''''''
sumDATA = 0
outputDATA = 0
countDATA = 0
sumELEVATED = 0
outputELEVATED = 0
countELEVATED = 0
outputrow = outputrow + 1
olddate = inputdate
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' okay with the outputting out of the way we're ready to start reading in the data.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
inputDATA = Worksheets(inputWKS).Cells(inputrow, dataCOL).Value

If Abs(inputDATA) < 6999 Then
sumDATA = sumDATA + inputDATA
countDATA = countDATA + 1
If inputDATA > 4 Then
sumELEVATED = sumELEVATED + inputDATA
countELEVATED = countELEVATED + 1
End If
End If
' next time step
inputrow = inputrow + 1
Loop
End Sub

Public Function sensor_avg(total As Double, count As Double)
' this function just returns an average
If count <> 0 Then
sensor_avg = total / count
Else:
total = 6999
End If
End Function

</pre>

Excel Macro Averages - Revision history

172.18.0.1: Created page with "
Option Explicit Private Sub daily_avg_sum_etc() ' worksheet variables Dim inputrow As Long Dim outputrow As Long Dim inputWKS Dim outputWKS Dim dataCOL..."