Count values within a row

[Tseug]

Hello!  I am trying to average results in a survey/list report.  Each person answers 10 questions using 0,1,2,3,4, or 5.  In the list, a row for each person taking the survey, each question's answer is listed going across, and at the end is the average.  I was simply saying ([Q1] + [Q2] + [Q3] + [Q4] + [Q5] + [Q6] + [Q7] + [Q8] + [Q9] + [Q10]) / (10) to get the average.

BUT, the client doesn't want the answers that contain zeros to count against the average.  So I need to figure out how many of the ten questions contain zeros, and then subtract this number from the number of questions to get my true average (if two of the ten questions contained zeros, I'd only divide by eight for that particular row).

Sounds easy enough on paper, but every time I try conceptualizing the work-around, I fry my brain.  Any suggestions are appreciated!   :)

[MFGF]

Hi,

You could add ten calculations - one for each question - with expressions as following:

Calc1 - if ([Q1] > 0) then (1) else (0)

Calc2 - if ([Q2] > 0) then (1) else (0)
etc.

Then add a calculation to add these, giving you the number of non-zero answers:
NumAnswers - [Calc1] + [Calc2] + [Calc3] etc

Finally, change your Average calculation to be
([Q1] + [Q2] + [Q3] + [Q4] + [Q5] + [Q6] + [Q7] + [Q8] + [Q9] + [Q10]) / [NumAnswers]

Good luck!

MF.

[Tseug]

Thanks!