Need Help Calculating Average Speed

[Guilio_Del_Fava]

Hi All, need help calculating the average speed of a trip. I know the formula in Excel but when i try that approach in the Data editor it gives me a weird number.

I have a distance column and a duration column. The duration column is using two DateTime Columns for the Start and End Time fo the Trip. I might have to convert the time using math column to get it to work but drawing a blank on how to do that.

Any help to point me in the right direction would be appreciated.

[Jeff_Hager]

How is your Duration column calculated?

Looks like it’s still holding on to that duration format HH:MM:SS. I think you can snap it out of that format with some math tricks… possibly multiplying by 1 once or twice. But for starters, I would like to know how that duration column is calculated.

[Guilio_Del_Fava]

Here is how it’s calculated. End timestamp - start timestamp.

[Darren_Murphy]

Assuming that you want to use duration in hours to calculate the average speed, change the math formula in your Duration column as follows:

Round(End-Start)*24

It should then work as expected in your Average Speed calculation.

[Guilio_Del_Fava]

@Darren_Murphy

Round(End-Start)*24

Hey Darren, I am just getitng a bunch if Zeros, would it work better to convert to minutes? The trips are short and non of them are over an hour.

[Darren_Murphy]

To get duration in minutes, multiply by 1440 instead of 24.

[Guilio_Del_Fava]

Hi Darren,

I am in the process of rebuliding my app after an accidental delete of the team folder. I am trying to get the average speed calc working as you have instructed in a previous reply but i am getting a bunch of zeros in that column.

My Duration column is simply subtracting the End Timestamp from the Start Timestamp to get the duration e.g 6:00 for example for 6 minutes.

I am trying to get the avarage speed from this Duration and the Trip Distance. I am not exactly sure what the formula should be for my Average Speed column?

[Jeff_Hager]

Can you show your math and the values you are plugging into it?

Date math always calculates the number of DAYS between dates, so if the difference is 12 hours, the result will be 0.5 days. That’s why you need to multiply by 24 to convert days to hours.

If the duration is 6 minutes, that a very small fractional number less than 1, so it may be calculating a very small fraction of 1 day and it’s just rounding off anything less than zero

Looking at @Darren_Murphy’'s formula, I think the Round just shouldn’t happen, or at least it should encapsulate the 24 as well. Rounding such a small duration that is less than half of a day is going to result in 0. Zero multiplied by 24 is zero.

• Remove the Round but keep the parentheses to get a highly accurate duration in hours.
• Or, add a left parenthesis before the existing left parenthesis, and a right parenthesis after the 24, so the round happens after you multiply the duration by 24. Slightly less accurate due to rounding, but you won’t have any numbers after the decimal.

[Guilio_Del_Fava]

Hi Jeff,

Here are some screenshots of my calculations based on your suggestions. One is the duration calc and the other the average speed calc. In the screeshots you can see a A a manual Average Speed column I have created of what the end result should be.

The result is coming out as duration it appears.

[Jeff_Hager]

You already converted to hours in the duration column. You don’t need to multiply by 24 a second time in the average speed column.

To correct the duration column, I think you can still add Round to the begining of the formula so you don’t get a result formatted as a duration. The parentheses are perfect as is. The only question would be if you want to round the duration to a whole number, or if you want some decimals included. You can specify the number of decimal places you want like this to get 2 decimal places.

Round((end-start)*24, 2)

[Guilio_Del_Fava]

@Jeff_Hager

Round((end-start)*24, 2)

Thank Jeff, that seems to have given me what I need.