calculating average with blank fields

[Annemarie]

I have a table that stores scores for agents, but sometimes there isn't a
score for a particular question. (Customer Satisfaction score 1-5, 5
questions). When I try to get the average for these agents, it's blank
because there is a blank field for a question. Basically, I want to ignore
that question/score and get the average for the fields filled out.
Question_1: 5
Question_2: 2
Question_3:
Question_4: 4
Question_5: 5

If it can't ignore the field, can I default it to 3 without manually
inputting 3 hundreds of times?

 

[Douglas J. Steele]

To have it default to 3, use:

(Nz(Question_1, 3) + Nz(Question_2, 3) + Nz(Question_3, 3) + Nz(Question_4,
3) + Nz(Question_5, 3))/5

To ignore the field, use

(Nz(Question_1, 0) + Nz(Question_2, 0) + Nz(Question_3, 0) + Nz(Question_4,
0) + Nz(Question_5, 0))/(IIf(IsNull(Question_1), 0, 1) +
IIf(IsNull(Question_2), 0, 1) + IIf(IsNull(Question_3), 0, 1) +
IIf(IsNull(Question_4), 0, 1) + IIf(IsNull(Question_5), 0, 1))

The second one will fail, of course, if none of the options have been
selected.

Incidentally, realistically your table isn't properly normalized. Fields
like Question_1, Question_2, etc. are called a repeating group, and
represent a violation of first normal form. What are you going to do when
suddenly there's a sixth question? Each question should be a row, not a
field. For a proper database design for questionnaires and surveys, see
Duane Hookom's "At Your Survey"
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

 

[Al Campagna]

Annemarie,
Are you saying that you want 3 for any unanswered Question?
(I'll use Q1, Q2, etc... you use your real control names)
= (NZ(Q1,3) + NZ(Q2,3)........ +NZ(Q5,3)) / 5
That will give Q3 a value of 3 for the Average calc.
OR
If you want an unanswered question to equal 0...
= (NZ(Q1,0) + NZ(Q2,0)........ +NZ(Q5,0)) / 5

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."