13 Replies Latest reply on Oct 8, 2010 10:04 AM by Lorette

    Please help - Average

    Lorette

      Title

      Please help - Average & Standard Deviation is not working

      Post

      Hi,

      I have the minimum, maximum and total working but the average and standard deviation is not working when I use the same method.

      Below are the formulas

      sTotalHours = Summary Field = Total of Hours

      sTotalCountA = Summary = Count of StructureID

      CFraction_B = CAlculation = 1 / GetSummary (sTotalCountA;STructureID)

      Total Structures = Summary Field = Total of CFraction_B (works)

      cTotalHoursStructureID = Calculat = GetSummary (sTotalHours; StructureID)

      MinimumHours = Summary Field = Minimum of cTotalHoursStructureID  (Works)

      MaxHours = Summary Field = Max of cTotalHoursStructureID   (Works)

      AvgHours = Summary Field = Average of cTotalHoursStructureID   (Doesn't Work)

      StandDevHours = Summary Field = Stand Deviation of cTotalHoursStructureID (Doesn't Work)

      Test Data

      Structure Type,  STructure Number,  Hours

      A, 22, 10 hrs

      A, 22, 20 hrs

      A, 23, 100 hrs

      A, 23, 200 hrs

      A, 24, 50 hrs

      K, 1, 8.5 hrs

      K, 1, 10 hrs

      K, 2, 20.5 hrs

      K, 2, 30 hrs

      V, 3, 15 Hrs

      V, 3, 20 Hrs

      V, 5, 50 Hrs

       

      Expected Results (By Structure Type)

      A

      Total Structures = 3  Total Hours = 440

      Minimum = 30  Maximum = 300

      Average = 126.67    Standard Deviation = 150.44 

      (Average & Standard Deviation formulas don't work)

      K

      Total Structures = 2 Total Hours = 69

      Minimum = 18.5    Maximum = 50.5

      Average =  34.5    Standard Deviation = 22.6

      (Average & Standard Deviation formulas don't work)

      V

      Total Structures =2  Total Hours = 85

      Minimum = 35   Maximum = 50

      Average =  42.5     Standard Deviation = 10.6

      (Average & Standard Deviation formulas don't work)

       

      Any help you can provide would be greatly appreciated. Thanks!

       

      ** Oct 8 , corrected get summary from "/" to ";"

        • 1. Re: Please help - Average & Standard Deviation is not working
          philmodjunk

          Try taking the average and standard deviation of the hours field instead of the sub totals.

          • 2. Re: Please help - Average & Standard Deviation is not working
            Lorette

            Hi,

            Taking an average and std dev of the hours field gives the wrong results.

            I am looking for the average time it takes to complete each structure, not average time of each line item.

            The average of the hours field returns

            A - 73.3   K - 17.5  V - 28.3

            My expected results are

            A: 126.67 hrs = (30 + 300 + 50) / 3

            K: 34.5 hrs = (18.5 + 50.5) / 2

            V:  42.5 hrs = (35 + 50) / 2

            Please help. Thanks.

            • 3. Re: Please help - Average & Standard Deviation is not working
              philmodjunk

              Once you have the summary fields so defined, you can use get Summary in a calculation or place the summary field in a sub summary part to get average and STDev for each structure.

              • 4. Re: Please help - Average & Standard Deviation is not working
                Lorette

                It is still not working...

                 

                Below are the expected results (Average)

                A: 146.67 hrs = (30 + 300 + 50) / 3

                K: 34.5 hrs = (18.5 + 50.5) / 2

                V:  42.5 hrs = (35 + 50) / 2

                 

                In reality I'm getting the following (averages):

                A: 146.67 hrs, K: 34.5 hrs (this is good!)

                V: 40.00 hrs <--- This is very concerning - How did FileMaker come up with that solution

                 

                Below are the formulas I used:

                sTotalHours = Summary Field = Total of Hours

                cTotalHoursStructureID = Calculation Field = GetSummary (sTotalHours / StructureID)

                AvgHours = Summary Field = Average of cTotalHoursStructureID  

                 

                Please let me know what I'm doing wrong.  Thanks.

                • 5. Re: Please help - Average & Standard Deviation is not working
                  philmodjunk

                  sAvgHours should be average of Hours.

                  cAvgHoursStructureID should be GetSummary ( AvgHours ; StructureID )

                  Not sure why all your posts use / which stands for division instead of ;.

                  • 6. Re: Please help - Average & Standard Deviation is not working
                    Lorette

                    It still isn't working. Is there a way I can send you screenshots?

                     

                     

                    • 7. Re: Please help - Average & Standard Deviation is not working
                      Lorette

                      I researched and saw your instructions about entering screenshots.

                      I created a photobucket account. I have included the table design, report design and results.  You will be able to see how the average and standard deviation are wrong.  Please let me know how to fix the issue.

                      http://s1184.photobucket.com/albums/z329/lpurschkevalard/Valard_FileMaker/

                       

                      Thanks,

                      Lorette

                      • 8. Re: Please help - Average & Standard Deviation is not working
                        philmodjunk

                        I see both what appear to be correct and incorrect field definitions for computing the average hours.

                        Two different summary fields compute as the "Average of Hours_ActivityStructure" I can't tell from your screen shots if either of these were placed on your layout and like any summary field, they should be placed in a sub summary part and sorted correctly if you want to see the average for that sub group not the average of all the records.

                        I can't find any standard deviation summary field defined as the "standard Deviation of HoursActivityStructure" and this is what you would need to compute the standard deviation.

                        Here is a demo file that you can compare to yours. It has the same data as your original post and computes both standard deviation and averages for all three structure ID's. The first layout shows the raw data. The second displays the summary fields.

                        http://www.4shared.com/file/-4RMyECG/StdDevAvgTest.html

                        • 9. Re: Please help - Average & Standard Deviation is not working
                          Lorette

                          Thanks for the demo file.

                          I added one table and one report and replicated my problem in this file.  The average and standard deviation calculations are wrong. 

                          I need to determine the average hours per structure for each structure type.

                          I created a new table "NewTable_StdDevAvgTest" with all the fields described above. I also created a new report "New_SummaryReportTest" based off the new table (sorted by structure type and structure id).  The minimum and maximums are correct , but averages and standard deviations are wrong.

                          Please download the file here.....

                          http://www.4shared.com/file/oiGEedAR/StdDevAvgTest_v2.html

                          Thank you for your all assistance, I`m looking forward to your advice :)

                           

                          • 10. Re: Please help - Average & Standard Deviation is not working
                            philmodjunk

                            Here is the corrected copy of your file:  http://www.4shared.com/file/YabBKkzJ/StdDevAvgTest_v2.html

                            All I did was swap your incorrectly defined summary fields for the correctly defined summary fields that were already defined in your table. I replaced NsAVG_Wrong with sAvg and NsSTDv_Wrong with sSTD.

                            I've now said this several times over: The summary fields must compute the average of and standard deviation of the original Hours field, not the calculation fields that compute the total hours. Both of these fields need to be able to count the individual values as well as summing either the values (average) or the squares of the values (standard deviation) in order to compute the correct results.

                            • 11. Re: Please help - Average & Standard Deviation is not working
                              Lorette

                              Thank you for your quick response and patience.

                              I require the following results for the average structure type calculation. I need the average time it takes to complete a structure within each different structure type category. Each structure type is completely different - some strutures types may take a week to complete, some may take a month.   I need the total time it takes to complete the structure, then compare it again every other structure in that structure type (category).

                              The calculation I require is the following:

                              A Average = 126.67 hrs = (30 + 300 + 50) / 3    {A22 = 30, A23 = 300, A24=50}

                              K Average = 34.5 hrs = (18.5 + 50.5) / 2        {K1 = 18.5, K2=50.5}

                              V Average =  42.5 hrs = (35 + 50) / 2              {V3 = 35, V5=50}

                               

                              Unfortunately, the average of the original hours field calculates the following results

                              A = 76  = (10 + 20 + 100 + 200 + 50) / 5

                              K = 17.25,   V = 28.3

                               

                              Is FileMaker capable of calculating my results (126.6, 34.5 & 42.5)? If so, what is the method to use?

                              Thank you very much for assistance. 

                              • 12. Re: Please help - Average & Standard Deviation is not working
                                philmodjunk

                                Apologies for the bum steers. It took me a bit to see the problem.

                                You have 5 values for A and so with the fields as defined, FileMaker adds the values and divides by 5 (the number of records) to get an average. While standard deviation uses a much more complex, it also uses the number of records physically present as part of the calculation. Hence, straight summary fields can't compute the values you want.

                                Short of using a related table where you have one record for each StructureID and Structure Type, you'll need to roll your own summary calculations here.

                                Average is do-able:  GetSummary ( sTotalHours ; StructureType )  / GetSummary ( nTotalStructures ; StructureType )

                                Standard deviation is much more complicated.

                                Define a calculation field for x squared: cXSquared as  cTotalHoursStructureID ^ 2  /  GetSummary ( sCount ; StructureID)

                                Define a summary field to total the squares as sXsquared: Total of cXsquared.

                                Now you can define a standard deviation calculation as:

                                Let ( n = GetSummary ( nTotalStructures ; StructureType ) ;
                                        GetSummary ( sXsquared ; StructureType ) / ( n -1 ) -
                                        GetSummary ( sTotalHours ; StructureType ) ^ 2 / ( n * ( n - 1 ) )
                                       ) ^ .5

                                • 13. Re: Please help - Average & Standard Deviation is not working
                                  Lorette

                                  Thank you very much. This seems to be working!