4 Replies Latest reply on Feb 15, 2012 3:44 PM by disabled_JackRodgers

    Calculating Average Speed in MPH



      Calculating Average Speed in MPH


      Taking a simple speed calculation, I can't seem to get FMP 7 to come up with the same result I can get in Excel.




      20 Miles Distance Traveled

      1 Hour to travel that distance


      I'm trying to get FMP to come to 20 as the result. 20/1 - doesn't seem difficult unless time is formatted as "TIME". 1:00:00 - which I need in this application.


      Setting it to "TIME" and my resulting figure as Average speed comes out .00555555555 MPH Which is obviously wrong without the *24. With the *24 it's .0002314814814815


      I can't seem to find any reference that will help me in this area. Anyone have an idea on this? I'm sure it's just trying to relate hours/minutes/seconds in FMP differently than Excel, but for the life of me I can't figure it out. 

        • 1. Re: Calculating Average Speed in MPH

          Hi Lex262


          What you need to do is convert the time field from being in time format to be in hours, you can do this by using the GetAsNumebr function to get the number of seconds and then dividing that by 3600. 


          This should do the trick. 


          Distance / ( GetAsNumber ( Time ) / 3600 ) 


          I hope this helps.

          • 2. Re: Calculating Average Speed in MPH

            Hi Lex262,

            Thanks for posting!

            The result of the 20/1 calculation is '.00555555....' because the Time is being evaluated by seconds.  Because of this, the calculation would look like 20/3600 (3600 seconds in an hour) to FileMaker.


            If you wanted to test this try the following: 

            -Create a Test field (Time) 

            -Create a Testc field (Calculation) w/ a calculated result as Number.  The calculation would be:  GetAsTime ( Test )

            If you entered 1:00:00 into the Test field, the calculation field would return 3600.  If you entered 1:30:10, the calculation field would return 5410.


            For your solution we can use the Hour, Minute, and Seconds functions.  Let's assume we have the following fields:

            -Distance (Number)

            -TravelTime (Time)

            -MPHc (Calculation) w/ calculated result as Number


            Let's set up our MPHc Calculation field so that it looks like the following:

            Hour ( TravelTime ) + (Minute ( TravelTime )/60) + (Seconds ( TravelTime )/3600)


            We now get the following calculated results for the following TravelTime values: 

            1:00:00 = 1 

            1:30:00 = 1.5

            1:30:10 = 1.50277... 


            We can now edit the MPHc calculation so that it looks like this:

            Distance /  (Hour ( TravelTime ) + (Minute ( TravelTime )/60) + (Seconds ( TravelTime )/3600)) & " " & "MPH"


            If you enter a value of '20' into the Distance field and a value of '1' or '1:00:00' into the TravelTime field you now get the result: 20 MPH


            *Note: I added the & " " & "MPH" to the MPHc calculation just as an extra.  It is not essential to the calculation. 


            Hope this helps or at least points you in the right direction.

            Let me know how everything works out!



            FileMaker, Inc. 

            • 3. Re: Calculating Average Speed in MPH
                 Thanks for your help. Understanding the fact that it relates in seconds makes all the difference. Worked like a charm.
              • 4. Re: Calculating Average Speed in MPH

                What if you use a time field and divide by that:

                distance / global time field

                Would Filemaker be smart enough to handle all of the conversions?


                And the answer at first testing seems to be yes, but you have to multiply by 60 for tics per second:


                (Distance / Time Field) * 60

                Distance is a number field.

                Time is a time field formatted for hhmmss and 24 hour.

                Much simpler.