Counting only values greater than Zero

[rbb101]

I have a form that includes numerious questions with rating scales from 0-4.
If someone enters a Zero, this represents "not applicable". Therefore I want
to exclude this entry from my calculations. I have an unboud box to
calculate the average for all the ratings, but a need a forumla that will
only count those values that are greater than zero, or that are not zero. In
otherwords, I need to exclude all the zeros or "not applicables" from the
denominator in the averages calculation.

 

[Ken Sheridan]

I assume it’s the average of ratings per record, not the averages of each
field over the set of records you want. If so the first thing I'd suggest is
that you identify the controls on the form to be used for the calculation by
setting the Tag property of each to CountMe.

Then add a function to the form's module:

Private Function AvgRating() As Double

Dim ctrl As Control
Dim intCount As Integer, intRating As Integer

For Each ctrl In Me.Controls
If ctrl.Tag = "CountMe" Then
If ctrl > 0 Then
intCount = intCount + 1
intRating = intRating + ctrl
End If
End If
Next ctrl

If intCount > 0 Then
AvgRating = intRating / intCount
End If

End Function

The set the ControlSource property of an unbound text box to:

=AvgRating()

Performance probably won't be lightning fast, however. It looks to me like
you are only having to resort to this because of a fundamental flaw in the
design; a very common one with databases of this type as it happens. Rather
than having multiple columns, one for each question, a correct design would
be to have multiple rows in a related table with columns Question
(referencing the key of a Questions table) and Answer, plus a foreign key
column referencing your current table. You can then use simple aggregating
queries to analyse the data.

Ken Sheridan
Stafford, England