Null values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to set up a calculation to skip a field if the value is null?
Say that the formula is; =[A]++[C] and if [C] is null, =[A]+. I have
tried using the Nz function but replacing null with a zero screws up the
average. Any help is most appreciated.

Richard Whittet
 
How are you calculating the average?

You should be able to get it with:
=Nz([A],0) + Nz(,0) + Nz([C],0) / CountItems([A], , [C])

Public Function CountItems(ParamArray varItems()) As Long
'Purpose: Count the number of non-null items passed in.
'Example: CountItems([A], , [C], [D])
Dim i As Integer
Dim lngCount As Long
For i = LBound(varItems) To UBound(varItems)
If Not IsNull(varItems(i)) Then
lngCount = lngCount + 1
End If
Next
CountItems = lngCount
End Function

If you have *lots* of repeating columns like this, the long-term solution
will be to create a related table to store all the values in one column. You
can then calculate the average with a very simple Totals query.
 
I am returning a result after using your function but the sum is always lower
than it actually is. Any ideas why it would do this? I am returning the
average by dividing the sum by a count of records which aren't null.

Allen Browne said:
How are you calculating the average?

You should be able to get it with:
=Nz([A],0) + Nz(,0) + Nz([C],0) / CountItems([A], , [C])

Public Function CountItems(ParamArray varItems()) As Long
'Purpose: Count the number of non-null items passed in.
'Example: CountItems([A], , [C], [D])
Dim i As Integer
Dim lngCount As Long
For i = LBound(varItems) To UBound(varItems)
If Not IsNull(varItems(i)) Then
lngCount = lngCount + 1
End If
Next
CountItems = lngCount
End Function

If you have *lots* of repeating columns like this, the long-term solution
will be to create a related table to store all the values in one column. You
can then calculate the average with a very simple Totals query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

RWhittet said:
Is there a way to set up a calculation to skip a field if the value is
null?
Say that the formula is; =[A]++[C] and if [C] is null, =[A]+. I
have
tried using the Nz function but replacing null with a zero screws up the
average. Any help is most appreciated.

Richard Whittet
Click to expand...
Click to expand...
 
Thank you very much. It is working exactly as I wanted it to now.

Richard

Allen Browne said:
How are you calculating the average?

You should be able to get it with:
=Nz([A],0) + Nz(,0) + Nz([C],0) / CountItems([A], , [C])

Public Function CountItems(ParamArray varItems()) As Long
'Purpose: Count the number of non-null items passed in.
'Example: CountItems([A], , [C], [D])
Dim i As Integer
Dim lngCount As Long
For i = LBound(varItems) To UBound(varItems)
If Not IsNull(varItems(i)) Then
lngCount = lngCount + 1
End If
Next
CountItems = lngCount
End Function

If you have *lots* of repeating columns like this, the long-term solution
will be to create a related table to store all the values in one column. You
can then calculate the average with a very simple Totals query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

RWhittet said:
Is there a way to set up a calculation to skip a field if the value is
null?
Say that the formula is; =[A]++[C] and if [C] is null, =[A]+. I
have
tried using the Nz function but replacing null with a zero screws up the
average. Any help is most appreciated.

Richard Whittet
Click to expand...
Click to expand...
 
Back
Top