Hi:
I want to omit null values from monthly averages I'm calculating for some in consistent data. Currently, the macro I wrote reads the empty cells and I believe is viewing them as zeros. When taking the monthly averages, in some cases on parameter 'X' might be sampled for a particular date at a location, but another parameter 'Y' isn't sampled for whatever reason on that particular date at the location. Therefore, there is no value in the cell for parameter 'Y' for that particular date (the value is null). When the macro runs, it sees the blank cell for the particular missing date, but I believe it still views that as a 0 value which it includes in the average, instead of overlooking that cell b/c it is an unknown. So, for example, it might thinks there are actually six actual sampling results instead of five, and calculates the average based on six being the total instead of five which seems to misrepresent the average. (e.g., it's currently viewing 2, 2, Null, 2, 2, 2 as n=6 instead of n=5)
If possible, I'd like to keep the structure of the code as below with only the minimal modifications to address this issue.
I do have Options-->Window Options-->Zero Values de-selected.
Here is my code for the macro as it currently stands:
Sub MonthlyAverage()
Dim WS1, WS2 As Worksheet
Set WS1 = Sheets("Station_Comprehensive_Cleaned")
Set WS2 = Sheets("Station_Averages")
Dim monthsum(1 To 12) As Double 'monthly sums
Dim count(1 To 12) As Integer 'for 12 months in a year
Dim ParamColumn As Integer
Dim SourceRows As Integer
'
' Enter the particular Parameter Column in "Station_Comprehensive_Cleaned" Here
ParamColumn = 18
'
' Enter the number of rows in "Station_Comprehensive_Cleaned" here
SourceRows = 52
'
For i = 1 To 12
count(i) = 0
monthsum(i) = 0 'makes the inital value for that month 0
Next i
'
For i = 2 To SourceRows
For j = 1 To 12 'Represents months of the year (e.g., 1=Jan, 2=Feb...etc)
If (Month(WS1.Cells(i, 2)) = j) Then 'Returns the month as a value from 1 thru 12
monthsum(j) = monthsum(j) + WS1.Cells(i, ParamColumn) 'Adds the value for that month for parameter in column of interest to "monthsum"
count(j) = count(j) + 1 'creates sample counts per month (e.g., j=1 for January with 2 samples for that month)
End If
Next j
Next i
'
' calculate and write the monthly averages to WS2
For i = 1 To 12
If (count(i) > 0) Then
WS2.Cells(i + 1, ParamColumn) = monthsum(i) / count(i)
End If
Next i
End Sub
Display More
attached is an example. I'm trying to calculate the monthly averages from the "Station_Comprehensive_Cleaned" worksheet.
thanks much!
--Tom