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

    Calculation Problem

    snkm

      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
          philmodjunk
            

          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
            snkm
              

            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
              philmodjunk
                

              Almost. Try

               

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

               

              for and integer answer.

               

              (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
                comment_1
                  

                How about a simple:

                 

                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
                  philmodjunk
                     Good ideas.
                  • 6. Re: Calculation Problem
                    snkm
                       Hmmm when I try this calculation, it still divides by all the fields...
                    • 7. Re: Calculation Problem
                      snkm
                         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
                        comment_1
                          

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

                        Which one?


                        • 9. Re: Calculation Problem
                          snkm
                            

                          Actually both.  :(

                          • 10. Re: Calculation Problem
                            snkm
                              

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

                             

                            ie:  field 1 = average of field a

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

                                  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
                                    snkm
                                      

                                    I have fieds named the following:

                                     

                                    Score_task1 = Score of task 1

                                    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):

                                     

                                    (Average_task1 + Average_task2 + Average_task3 + Average_task4 + Average_task5 + Average_task6 + Average_ task7 + Average_ task8 + Average_ task9 + Average_ task10)

                                    / Count ( Average_task1; Average_task2; Average_task3; Average_task4; Average_task5; Average_task6; Average_ task7; Average_ task8; Average_ task9; Average_ task10)

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

                                    (Score_task1 + Score_task2 + Score_task3 + Score_task4 + Score_task5 + Score_task6 + Score_ task7 + Score_ task8 + Score_ task9 + Score_ task10) / Count (Score_task1; Score_task2; Score_task3; Score_task4; Score_task5; Score_task6; Score_ task7; Score_ task8; Score_ task9; Score_ task10)

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

                                    Average(Score_task1; Score_task2; Score_task3; Score_task4; Score_task5; Score_task6; Score_ task7; Score_ task8; Score_ task9; Score_ task10)

                                     

                                     

                                    1 2 Previous Next