[Solved] How to calculate Average speed

[blakemulder]

Hello! I have a spreadsheet of cycling perfomance data with Time and Distance from which I wish to calculate average speed in KPH. Time is in this format 00:42:15 (42mins 15 seconds) and distance in km (24.6). Average speed is calculated distance divide by time but do I need to convert time to a decimal? Could anybody supply me with a formula to calculate average speed please? I've tried googling, and I can find an online calculator but I have hundreds of entries so I want Calc to do the legwork for me.

Thankyou if you can help

[karolus]

Times are internal fractions of Day…

Code: Select all

=distance/time/24 

does the trick

[keme]

First you need to understand that spreadsheet time data is based on the "day" unit. Hours given will be a fraction of a day. So e.g. six hours, entered and displayed as 6:00:00, will be stored as (and give calculation results as) the fraction 1/4 (or decimal 0.25). In other words, 6:00:00 is how the decimal number is rendered in "time context".

To convert that "fraction of a day" to hours, multiply by 24.

So, if you have time data in column A and distance in column B, cell C3 may contain the formula =B3/(24*A3)

Edit: I see Karolus beat me to it. My formula above can also be simplified to his symbolic formula as (specifically) =B3/A3/24 (or =B3/24/A3 if you like) which yields the same result. I think that the parentheses makes the formula convey the logic behind the calculation better. A matter of taste, I guess...

[blakemulder]

Thankyou very much! will work on it....