Ask Your Question
0

Distance, Time = Kilometres per hour

asked 2017-12-10 19:55:19 +0200

PDM88 gravatar image

Hello. I'm trying to create a database for cycling events, and results.

The current spreadsheet I'm using has:

Cell A2 Distance 294.00 km (formatted as text "@ km" user defined) Cell A3 Time (7,7,59)" (formatted as Time "[HH]:MM:SS.00" user defined) data entered as "=TIME(7,7,59)" Cell A4 Speed in Km/h - can't understand how to do this unfortunately.

Any help appreciated. Thank you.

edit retag flag offensive close merge delete

Comments

Here is what I have at present Distance Time Speed Cell A2 Displays 294.00 km Cell A3 Displays: 07:07:59:00 Cell A4 Displays: ERR Format: 0.00" km" Format: [HH]:MM:SS.00 Format: 0.00" km/h" Data: 294 Data Input =TIME(7,7,59) Data Input

PDM88 gravatar imagePDM88 ( 2017-12-11 15:52:16 +0200 )edit

I cannot show how to find the speed of the race. I have the time - correctly stated as above HH MM SS 00 ie 7hrs 7 minutes 59 seconds The race distance is 294 km - displayed correctly with new format etc

what i cannot get is the speed of the race - it is .41.2651 km/h average.

I cannot find the method to calculate automatically (in the cell) with distance and time inputted only.

PDM88 gravatar imagePDM88 ( 2017-12-11 16:07:36 +0200 )edit

If the distance in A2 is given as the text "294.00 km" you cannot use it in the quotient A2 / A3. There is a habit of automatic conversion in spereadsheets, but it cannot work in this case due to th unit "km".

Lupp gravatar imageLupp ( 2017-12-11 20:39:35 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2017-12-10 20:09:59 +0200

updated 2017-12-10 20:11:49 +0200

  1. Use 0.00" km" format for A2.
  2. Use =A2/A3/24 formula for A4.
edit flag offensive delete link more
0

answered 2017-12-10 20:21:05 +0200

Lupp gravatar image

updated 2017-12-10 20:32:58 +0200

Speed = Distance / Duration
Durations are mostly handled in the same way as TOD in spreadsheets. Time values to which the standard formatting shall be applicable must use the unit d (1 day), and the TIME() function knows that. 1 d = 24 h . To get the number of hours such a duration has (as a number with fractional part) you need to multiply the number of days with 24.

Speed / (km/h) = (Distance / km) / ((Duration / d) * (1d / 1h ))  
Speed / (km/h) = (Distance / km) / ((Duration / d) * 24 )

In C4: =A2 / (A3*24)
This applies if A2 contains a number (reasonably). If it contains text like "294.00 km" e.g. (disadvisable) you firstly depend on the locale using the point as decimal separator, and secondly you need to get rid of the "km". Try to use in the above formula instead of A3 the expression VALUE(SUBSTITUTE(A3;"km";"").

edit flag offensive delete link more

Comments

Here is what I have at present Distance Time Speed Cell A2 Displays 294.00 km Cell A3 Displays: 07:07:59:00 Cell A4 Displays: ERR Format: 0.00" km" Format: [HH]:MM:SS.00 Format: 0.00" km/h" Data: 294 Data Input =TIME(7,7,59) Data Input

PDM88 gravatar imagePDM88 ( 2017-12-11 20:16:28 +0200 )edit

The spreadsheet does not calculate with what a cell displays, but with what it contains. Is the content of A2 a number or is it a text? Got to any blank cell and enter =ISNUMBER(A2). What is the answer you get?

Lupp gravatar imageLupp ( 2017-12-11 20:42:31 +0200 )edit

the answer i see is "TRUE"

PDM88 gravatar imagePDM88 ( 2017-12-12 15:20:48 +0200 )edit
0

answered 2017-12-10 22:29:50 +0200

Lew Merrick gravatar image

But, how about Furlongs/Fortnight?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-10 19:55:19 +0200

Seen: 249 times

Last updated: Dec 10 '17