Omit Null Values Calculating Average

turkishgold · Oct 12th 2007

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:

Code

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

**Andy Pope** · Oct 12th 2007

Re: Omitting Null Values When Calculating Averages

Check the cell has content before adding it to running total and base.

[vba]Sub MonthlyAverage()
Dim i, j
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
If Len(WS1.Cells(i, ParamColumn)) > 0 Then
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)
Exit For
End If
Next j
End If
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
[/vba]

**StephenR** · Oct 12th 2007

Re: Omitting Null Values When Calculating Averages

If I understand correctly, this line

Code

count(j) = count(j) + 1 'creates sample counts per month (e.g., j=1 for January with 2 samples for that month)

means that count will be incremented by 1 even if there is a blank so that's why blanks will be included in your average.

Why not avail yourself of the splendidly apposite built-in AVERAGE function? Or add an If statement to only increment count if the cell is not blank.

Bill Rockenbach · Oct 12th 2007

Re: Omitting Null Values When Calculating Averages

Change:

Code

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)

TO:

Code

monthsum(j) = monthsum(j) + WS1.Cells(i, ParamColumn) 'Adds the value for that month for parameter in column of interest to "monthsum"
                if monthsum(j) <> 0 then count(j) = count(j) + 1 'creates sample counts per month (e.g., j=1 for January with 2 samples for that month)

DWildman · Oct 12th 2007

Re: Omitting Null Values When Calculating Averages

You just need to check if the cell contains a value before bumping the count up. Like this...

Code

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"
' ADDING THIS IF STATEMENT WILL GIVE YOU THE CORRECT RESULTS                
If WS1.Cells(i, ParamColumn) <> "" Then
                    count(j) = count(j) + 1 'creates sample counts per month (e.g., j=1 for January with 2 samples for that month)
                End If
            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

turkishgold · Oct 12th 2007

Re: Omitting Null Values When Calculating Averages

thanks!!!

p45cal · Oct 13th 2007

Re: Omitting Null Values When Calculating Averages

Coming in a bit late on this one (9 hours) and going directly against "If possible, I'd like to keep the structure of the code as below with only the minimal modifications to address this issue", I've checked the results of the following in several places and it seems right (but check!), but it does do all the columns and it is quite short (all the calculations are finished by the first 4 lines):

Code

Sub blah()
With Sheets("Station_Averages")
    .Range("C2").FormulaArray = _
            "=AVERAGE(IF(ROW()-1=MONTH(Station_Comprehensive_Cleaned!R2C2:R52C2),IF(ISNUMBER(Station_Comprehensive_Cleaned!R2C:R52C),Station_Comprehensive_Cleaned!R2C:R52C)))"
    .Range("C2").AutoFill Destination:=.Range("C2:C13"), Type:=xlFillDefault
    .Range("C2:C13").AutoFill Destination:=.Range("C2:AS13"), Type:=xlFillDefault
    'Now clean up error cells and convert to values:
    For Each cll In .Range("C2:AS13")
    If IsError(cll.Value) Then
    cll.Value = ""
    Else
    cll.Value = cll.Value
    End If
    Next cll
End With
End Sub

Display More

p45cal

Dave Hawley · Oct 13th 2007

Re: Omitting Null Values When Calculating Averages

What's the reason for a macro over a cell formula where the result can be read from?

p45cal · Oct 13th 2007

Re: Omit Null Values Calculating Average

The cell formula that I used was long (I'm sure it could have been shorter, but I stopped developing it as soon as I got the right answer) and I couldn't incorporate the
if(iserr(longformula,"",longformula again)
construct since it became too long. The results produced a lot of div by 0 errors where there were no values to average. The macro cleans that up.
Being a long formula it would be easy to introduce mistakes so I set it in stone, so to speak, in the macro.
Disadvantages include having to change the macro if the number of records changes, although it could easily be accommodated with an extra line (maybe 2), but it was way past my bedtime.
If I were to spend more time on it I should be able to shorten the formula (perhaps with sumproduct) to a point where it's more manageable without a macro.

p45cal

Dave Hawley · Oct 14th 2007

Re: Omit Null Values Calculating Average

I would mannually Enter a Daverage Function in a named cell, with reference to some criteria in a named range. Then read back the result into code and modify the criteria (code or manual) as and when needed.

Omit Null Values Calculating Average

Participate now!

Share