Average of 3 fields

[jset818]

I have 3 fields, numeric, they're all working days. Some of the fields are blank. I'm currently using this formula, it's look fine to me on paper but when I run it, it only works when all 3 fields have value. I get no average if field 2 or 3 is blank. See screenshot.



Field 1= Availability. Field 2= 2nd Sourcing Time. Field 3= 3rd Sourcing Time

Average: IIf([Availability]="",Null,IIf([2nd Sourcing Time]="",Null,IIf([3rd Sourcing Time]="",Null,([Availability]+[2nd Sourcing Time]+[3rd Sourcing Time])/(IIf(IsNull([Availability]),0,1)+IIf(IsNull([2nd Sourcing Time]),0,1)+IIf(IsNull([3rd Sourcing Time]),0,1)))))

[June7]

Use Nz(): (Nz(field1,0) + Nz(field2,0) + Nz(field3,0)) / ...

Do you want the field to be counted if it is Null?

[NTC]

a blank will not perform math; it is not viewed as 0 which is why J7's advice is forcing in a 0 if null.

beyond that point is the definition of 'average' for you; as to whether it should always divide by 3 or must dynamically decide what is to be the denominator value which is a bit trickier but do-able

[jset818]

Edit: I found this module from another thread on this forum. The original poster had the same issue as me. The module works perfectly however I wondering if there's a way to make a small change to it. I'm not very experienced in VBA modules. When all fields are blank, rather than leaving the Average 0, can we change it to null?

Public Function Average_Of_Fields(ParamArray flds() As Variant) As Double
Dim i As Byte, sumFields As Long, numFields As Byte

For i = LBound(flds) To UBound(flds)
If Len(flds(i) & vbNullString) > 0 Then
sumFields = sumFields + flds(I)
numFields = numFields + 1
End If
Next

If numFields <> 0 Then
Average_Of_Fields = sumFields / numFields
End If
End Function

[June7]

I use functions found at http://support.microsoft.com/kb/209839

[NTC]

looping code is not what I would recommend; it won't scale in large record sets having to run loop code for each record.

One can do it with nested IIF statements but is complicated to trouble shoot and so is often easier to spread things out in separate fields; make 3 new fields: F1, F2, F3 whereby
F1: iif(IsNull([Availability]),0,1)
repeat that idea for F2, F3 for 2nd & 3rd sourcing

so now you will have side by side either 1s or 0s for F1-3 depending on whether on not there are values in those fields when you run the query

then add a new field
Denominator: F1+F2+F3

and now you can calc your average.... in large record sets this will run a lot faster