OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calculate speed (km/h) from distance (m) & time (hh:mm:s

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
televertical
Newbie
Newbie


Joined: 16 Mar 2010
Posts: 4

PostPosted: Tue Mar 16, 2010 8:30 am    Post subject: Calculate speed (km/h) from distance (m) & time (hh:mm:s Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Mar 16, 2010 8:46 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
televertical
Newbie
Newbie


Joined: 16 Mar 2010
Posts: 4

PostPosted: Tue Mar 16, 2010 9:54 am    Post subject: Reply with quote

ok, thanks for your reply.

Could you tell me how to convert hh:mm:ss to days in calc, as you describe?
Back to top
View user's profile Send private message
Robert Tucker
Moderator
Moderator


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

PostPosted: Tue Mar 16, 2010 12:20 pm    Post subject: Reply with quote

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)
Back to top
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Tue Mar 16, 2010 2:29 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
televertical
Newbie
Newbie


Joined: 16 Mar 2010
Posts: 4

PostPosted: Thu Mar 18, 2010 11:02 pm    Post subject: Reply with quote

(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))
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Mar 19, 2010 12:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Fri Mar 19, 2010 3:54 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
televertical
Newbie
Newbie


Joined: 16 Mar 2010
Posts: 4

PostPosted: Fri Mar 19, 2010 4:45 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Mar 19, 2010 5:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group