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.
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
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.
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”.
0.00" km" format for A2.=A2/A3/24 formula for A4.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";"").
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
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?
the answer i see is “TRUE”
But, how about Furlongs/Fortnight?