# Omit Null Values Calculating Average

• 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:

attached is an example. I'm trying to calculate the monthly averages from the "Station_Comprehensive_Cleaned" worksheet.

thanks much!
--Tom

## Files

• 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]

[h4]Cheers
Andy
[/h4]

• 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.

• 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)``````

[FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
Tip: To avoid chasing code always use Option Explicit.

• 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...

• Re: Omitting Null Values When Calculating Averages

thanks!!!

• 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):

p45cal

• 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?

• 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

• 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.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!