It sounds as if the table design is not normalized, but contains a field for
each question response. Since the original poster (OP) may be stuck with
the design for now, it would probably be best to build a function to get the
average.
Assumption:
-- responses to each survey are all in one row. SurveyID, Answer1, Answer2,
Answer3,...Answer152
-- responses are numeric ( or at least the ones to be averaged are numeric)
-- 0 should be averaged, null (blank) should be ignored
Using the following function for the example given
Field: AvgOfFour: fGetMeanAverage([Expr201],[Expr202],[Expr202A],[Expr203])
Paste the following into a code module and save the module (with some name
other than fGetMeanAverage).
'/====== Code Follows
Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3
(21/7)
'Ignores values that cannot be treated as numbers.
Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0
For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i
If intElementCount > 0 Then
'At least one number in the group of values
fGetMeanAverage = dblSum / intElementCount
Else
'No number in the group of values
fGetMeanAverage = Null
End If
End Function
'/====== Code ends
Jeff Boyce said:
In your query, in design mode, on the row for Criteria, under the field,
put something like:
<>0
But again, if the field holds the count of something, then 0 has meaning.
Regards
Jeff Boyce
Microsoft Office/Access MVP
scutcher said:
Thanks for your reply. However, since I'm such a novice, can you tell me
how
you would spell that out by excluding the "0"s?
I created this database to track survey responses, which are grouped into
13
sets of questions. Most sets contain 5-10 questions. For each survey
(record), I would like to get an average of each question set, then an
average of all sets. But, I can't get this averege if one of the question
sets contain an #Error response.
Jeff Boyce said:
Consider excluding values of "0" in your query. That is, if 0 means
"nothing", rather than 0 meaning "none".
I can take an average of a set of numbers that includes values of 0 and
get
a meaningful average. Based on the expression you provided, I wonder if
your data might be better handled in a spreadsheet (repeating columns)
than
in a relational database table (repeating rows)...
Regards
Jeff Boyce
Microsoft Office/Access MVP
I see many types of posts regarding the problem of averaging null
values. I
have made it work for my queries, except it won't work when all the
values
are zero.
The following is averaging three values. If all the values equal 1 or
greater, it works perfectly. But if they all equal zero, I get an
#Error.
Expr201=0
Expr202=0
Expr203=0
ExprSec2: IIf(Nz([Expr201],0)=0 And Nz([Expr202],0)=0 And
Nz([Expr202a],0)=0
And
Nz([Expr203],0)=0,0,Nz([Expr201],0)+Nz([Expr202],0)+Nz([Expr203],0))/(IIf(Nz([Expr201],0)=0,[Expr201],1)+IIf(Nz([Expr202],0)=0,[Expr202],1)+IIf(Nz([Expr203],0)=0,[Expr203],1))
Please help!
