Average 4 fields

[John Nelson]

This has got to be easy, but I can't find an answer.
I want to average 4 fields (there may be missing data, so
I can't divide by 4). I want to do this for a record and
in a query.
The 'Avg' function seem to only apply to 1 field.

 

[Duane Hookom]

Averaging across fields generally suggests un-normalized data. However, try
(Val(Nz(fldA,0)) + Val(Nz(fldB,0)) + Val(Nz(fldC,0)) + Val(Nz(fldD,0))) /
(IsNull(fldA)+IsNull(fldB)+IsNull(fldC)+IsNull(fldD))

 

[John Nelson]

My Hero... except I needed "(Val(Nz(fldA,0)) + Val(Nz
(fldB,0)) ...)/(4 - IsNull(fldA)+IsNull(fldB)...)

 

[Duane Hookom]

If you are storing more than one score per record then you are not
normalized pretty well. A more normalized database would have each score in
its own record. Regardless, if you can work with your existing structure
then it's your issue when you want to do calculations like averaging 4
fields rather than averaging one field based on a simple grouping in a
totals query.

--
Duane Hookom
MS Access MVP

VERY NICE THANKS
I think the data in normalized pretty well. The data is
Pre-K student evaluation scores -- 7 categories with 2-6
subcategories and 2-4 evaluation items per subcategory.
I needed to average the subcategories to reduce the data
to something that the customer can grasp in a bar chart.
Sometimes data is missing.

-----Original Message-----
John,

The need to do this generally indicates that your data is not properly
normalized. Having said that, I have frequently run across poorly
normalized data tables and developed a function similiar to what I
have posted below to assist on those occassions.

X = AvgValue([FldA], [FldB], [FldC], [FldD])

There is no error checking in this function as it is, so you will have
to ensure that the data you pass to it is either numeric, can be
interpreted as numeric, or Null. The nice thing about this is that
you can pass any number of values, and can even get the midpoint
between two dates by using it like:

X = CDate(AvgValue(#1/1/2003#, #7/1/2003#))

Keep in mind that if all the values passed to the function are Null,
the result will be Null.

Public Function AvgValue(ParamArray args()) As Variant

Dim Numerator As Variant, denominator As Integer
Dim intLoop As Integer

denominator = 0
For intLoop = LBound(args()) To UBound(args())

If Not IsNull(args(intLoop)) Then
Numerator = Numerator + args(intLoop)
denominator = denominator + 1
End If

Next

If denominator = 0 Then
AvgValue = Null
Else
AvgValue = Numerator / denominator
End If
End Function

--
HTH

Dale Fye


This has got to be easy, but I can't find an answer.
I want to average 4 fields (there may be missing data, so
I can't divide by 4). I want to do this for a record and
in a query.
The 'Avg' function seem to only apply to 1 field.


.