[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
televertical
Newbie

Joined: 16 Mar 2010
Posts: 4

 Posted: Tue Mar 16, 2010 8:30 am    Post subject: Calculate speed (km/h) from distance (m) & time (hh:mm:s Hello, noob first question in this forum. I am sure it is possible to find the solution, but perhaps ill save some time by asking. I have time in cell A1 as HH:MM:SS and distance in A2 as metres (m), want to calculate accurate speed in any format, preferably km/h but any will do... Could anyone give me an example of how to do this? Any help greatly appreaciated. Cheers
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue Mar 16, 2010 8:46 am    Post subject: All times are formatted numbers in unit "Days". =A1/B1/24/1000 [A1 in meters, B1 in days] gives km/h_________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
televertical
Newbie

Joined: 16 Mar 2010
Posts: 4

 Posted: Tue Mar 16, 2010 9:54 am    Post subject: ok, thanks for your reply. Could you tell me how to convert hh:mm:ss to days in calc, as you describe?
Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3407
Location: Manchester UK

 Posted: Tue Mar 16, 2010 12:20 pm    Post subject: If you have HH:MM:SS in A1, say, you can put =A1 in any other cell and it will give the value in days. Villeroy's formula does what you first requested, I believe._________________OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

 Posted: Tue Mar 16, 2010 2:29 pm    Post subject: An example to clarify: the time 12:00:00 in a cell is actually stored as the number 0,5 (because the time unit is one day, and 12 hours equals half a day). You can verify that by first entering 0,5 in a cell (or 0.5 if you're using period for decimal point), then formatting that cell as HH:MM:SS. When calculating with time formatted numbers, you need to take this into account, multiplying by a factor of 24 to convert the number to "hours base". That's why the 24 is required in Villeroy's formula.
televertical
Newbie

Joined: 16 Mar 2010
Posts: 4

 Posted: Thu Mar 18, 2010 11:02 pm    Post subject: (Im using Calc in google docs i might add.. not sure if this matters.) Cant make this work as you describe, i have distance i.e. "3300" in A1 and Time i.e. "0:32:56" in B1. (Time in B1 is formulated from =TIME(H11,I11,J11))
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Fri Mar 19, 2010 12:42 am    Post subject:

 Quote: (Im using Calc in google docs i might add.. not sure if this matters.)

So we do not talk about OpenOffice.org at all.

 Quote: Cant make this work as you describe, i have distance i.e. "3300" in A1 and Time i.e. "0:32:56"

"Does not work" does not tell anything.
In any spreadsheet I know it works like this:
=A1/B1/24/1000 => 6,0121 km/h (3300 m in 33 minutes)
because unformatted 0:32:56 in B1 is equivalent to decimal 0.0229.
33 minutes ~= 0.0229 days.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

Posted: Fri Mar 19, 2010 3:54 am    Post subject:

 televertical wrote: (Im using Calc in google docs i might add.. not sure if this matters.) Cant make this work as you describe, i have distance i.e. "3300" in A1 and Time i.e. "0:32:56" in B1. (Time in B1 is formulated from =TIME(H11,I11,J11))
It works exactly the same in a Google docs spreadsheet. With your data and Villeroy's formula i get just above 6 km/h.

What do you get from that formula (wrong numeric result, zero, error message, or something else)?

You can check the actual time value (i.e. not time formatted) by using the 123 icon on the toolbar, then select the upper option, "Normal". You should get around 0.0229.
televertical
Newbie

Joined: 16 Mar 2010
Posts: 4

 Posted: Fri Mar 19, 2010 4:45 am    Post subject: Solved.. Sorry for not being clear; by "not working" i meant the result was on the wrong format... Your formula works great Villeroy, thanks! Teme's hint on the 123 icon was the ticket.. Thanks alot for quick and good help!
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Fri Mar 19, 2010 5:42 am    Post subject: Is there no help forum for your web-based spreadsheet application? Why do we waste our time here without even knowing what application you are talking about?_________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum