3 Replies Latest reply on Feb 2, 2011 1:37 PM by philmodjunk

# Calculating average while excluding specific fields

### Title

Calculating average while excluding specific fields

### Post

Hi all,

I'm working on a customer survey database/layout. The possible scores for each question are 1 - 5, however, if a question is not relevant to a specific customer then the surveyer enters a 0 for that question.

I would like to calculate an average score, taking into account that questions with a score of 0 should not be included in the average (including the question in the denominator will screw up the average). What calculation logic would I use to exclude the '0's from the count?

Thanks!

-Ryan

• ###### 1. Re: Calculating average while excluding specific fields

Make that score an empty field instead of a zero and an Average summary field will ignore that value.

If you want to keep the zero, define a calculation field such as If ( Response ≠ 0 ; Response ; "" )

Then an average of this calculation field will omit the 0 values.

• ###### 2. Re: Calculating average while excluding specific fields

Excellent, thanks! I used a slightly different method (found here: http://help.filemaker.com/app/answers/detail/a_id/5005/~/average-of-data-in-separate-fields) but I think I've got it working now.

On the graph, how can I specify to show only the 5 most recent surveys for that contact? I think using the counter field (so the 5 highest values in the counter field) is my best option.

Thanks!

-Ryan

• ###### 3. Re: Calculating average while excluding specific fields

I'm afraid I can't answer as I have no idea how you've structured your system nor what chart type you have in mind. The KB article is for averaging records defined within the same record. Putting the scores in separate records--which is what I had assumed--is generally a better database structure as it is more flexible to work with.

The KB article is for much older versions of FileMaker. With current versions, Average ( field1 ; field2; field3 ) will compute the average and blank fields will not be included as part of the average.