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
attached is an example. I'm trying to calculate the monthly averages from the "Station_Comprehensive_Cleaned" worksheet.