1 2 Previous Next 17 Replies Latest reply on Apr 16, 2009 6:04 PM by comment_1

# Calculation Problem

### Title

Calculation Problem

### Post

I have a field called OVERALL STATS.  To populate OVERALL STATS, fields 1 through 10 are added together and divded by 10.

So what I need to to have the OVERALL STATS populate even when fields 1 through 10 don't all have data.  Let's say only 6 fields are populated.  I then need these feilds added together and divided by 6.

Here is my temporary solution that only works when either 9 or 10 fields are populated:

If ( IsEmpty ( 1 ) ;  (2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10)/9 ;  (1+ 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10)/10)

I can do this in excel by dividing by counta, but don't know how to do it filemaker.  If anyone knows, please help.

• ###### 1. Re: Calculation Problem

Divide by Count(field1, field2, field3, field 4....

count should return the number of nonblank fields which is what you requested.

• ###### 2. Re: Calculation Problem

Like this:

Div(1+2+3+4+5+6+7+8+9+10);count(1+2+3+4+5+6+7+8+9+10)

Is this right, cuz I can't seem to get it to work.  I keep getting a message saying there are too few peramaters.

• ###### 3. Re: Calculation Problem

Almost. Try

Div(1+2+3+4+5+6+7+8+9+10);count(1; 2; 3; 4; 5; 6; 7; 8; 9; 10)

(1+2+3+4+5+6+7+8+9+10) / count(1; 2; 3; 4; 5; 6; 7; 8; 9; 10)

will compute it as a real number.

PS, I'd really consider renaming the fields to be fld1, fld2 ... instead of 1, 2 ... if I were you :smileywink:

• ###### 4. Re: Calculation Problem

Average ( field1 ; field2 ; field3 … field10 )

BTW, it would probably be better to use RECORDS in a related table instead of FIELDS.

• ###### 5. Re: Calculation Problem
Good ideas.
• ###### 6. Re: Calculation Problem
Hmmm when I try this calculation, it still divides by all the fields...
• ###### 7. Re: Calculation Problem
lolz I have field names BTW just didn't wat to write them all out in here.  Figured numbers would be easiest.  Thanks anywayz though.  :)
• ###### 8. Re: Calculation Problem

snkm wrote:
Hmmm when I try this calculation, it still divides by all the fields...

Which one?

• ###### 9. Re: Calculation Problem

Actually both.  :(

• ###### 10. Re: Calculation Problem

The fields I am adding togehter are already averaged fields.  Does that matter?

ie:  field 1 = average of field a

• ###### 11. Re: Calculation Problem
I'd just use Comments suggestion Average(field list)
• ###### 12. Re: Calculation Problem
No, it doesn't - make sure that the fields are really empty.
• ###### 13. Re: Calculation Problem

snkm wrote:

The fields I am adding togehter are already averaged fields.  Does that matter?

ie:  field 1 = average of field a

It depends on what you are doing and what do you expect. An average of averages is NOT the same as the average of the original population.

• ###### 14. Re: Calculation Problem

I have fieds named the following:

Average_task1 = Average of the above field

I have these fields for each task (10 tasks in all).  Ultimately I need 1 score for each person which includes adding tasks 1 through 10 together and dividing by the number of tasks.

I've tried all of the following (none of which produce an accurate score):

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------