Time, Distance, Speed
 

I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

=N(A2/(B2*24))

where A2 is the distance, B2 the time.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michael Koerner" wrote in message
...
I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

Speed = distance moved / time taken
The distance moved is A2-A1
Time taken is B2-B1
Speed is =(A2-A1)/(B2-B1) and the parentheses are needed to make sure Excel
does the subtractions before the division

If the series of measurements are for a non-accelerating object maybe your
task (and this does sound like homework!) is to fit the data to a straight
line. Make a chart and add a trendline; then look in Help to lean about the
SLOPE function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael Koerner" wrote in message
...
I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

On Mon, 9 Mar 2009 07:35:43 -0400, "Michael Koerner" wrote:

I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

If 00:52:30.15 is elapsed time in hrs:minutes:seconds then
elapsed time in hours = left(elapsed time,2) + mid(elapsed time,4,2)/60 +
mid(elapsed time,7,5)/120
and
Kilometers per hour =Kilometers/elapsed time in hours

5.0/(00 + 52/60 + 30.15/120) = 4.47 Km/Hr

Chuck

Time, Distance, Speed
 

Bob;

It does, and thank you very much

--

Regards
Michael Koerner


"Bob Phillips" wrote in message
...
=N(A2/(B2*24))

where A2 is the distance, B2 the time.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Michael Koerner" wrote in message
...
I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

Thanks Bernard. A little to far gone to be a student. well into my 7th
decade <g

--

Regards
Michael Koerner


"Bernard Liengme" wrote in message
...
Speed = distance moved / time taken
The distance moved is A2-A1
Time taken is B2-B1
Speed is =(A2-A1)/(B2-B1) and the parentheses are needed to make sure Excel
does the subtractions before the division

If the series of measurements are for a non-accelerating object maybe your
task (and this does sound like homework!) is to fit the data to a straight
line. Make a chart and add a trendline; then look in Help to lean about the
SLOPE function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael Koerner" wrote in message
...
I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

Chuck;

Thanks, will try it out.
--

Regards
Michael Koerner


"Chuck" wrote in message
...
On Mon, 9 Mar 2009 07:35:43 -0400, "Michael Koerner"
wrote:

I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

If 00:52:30.15 is elapsed time in hrs:minutes:seconds then
elapsed time in hours = left(elapsed time,2) + mid(elapsed time,4,2)/60 +
mid(elapsed time,7,5)/120
and
Kilometers per hour =Kilometers/elapsed time in hours

5.0/(00 + 52/60 + 30.15/120) = 4.47 Km/Hr

Chuck

Time, Distance, Speed
 

Sorry. I just started to seventh decade last month!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael Koerner" wrote in message
...
Thanks Bernard. A little to far gone to be a student. well into my 7th
decade <g

--

Regards
Michael Koerner


"Bernard Liengme" wrote in message
...
Speed = distance moved / time taken
The distance moved is A2-A1
Time taken is B2-B1
Speed is =(A2-A1)/(B2-B1) and the parentheses are needed to make sure
Excel
does the subtractions before the division

If the series of measurements are for a non-accelerating object maybe your
task (and this does sound like homework!) is to fit the data to a straight
line. Make a chart and add a trendline; then look in Help to lean about
the
SLOPE function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael Koerner" wrote in message
...
I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

Youngster <g Also on the MVP list. Like to post in the public ng just in
case what I ask is causing problems for others.

--

Regards
Michael Koerner


"Bernard Liengme" wrote in message
...
Sorry. I just started to seventh decade last month!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael Koerner" wrote in message
...
Thanks Bernard. A little to far gone to be a student. well into my 7th
decade <g

--

Regards
Michael Koerner


"Bernard Liengme" wrote in message
...
Speed = distance moved / time taken
The distance moved is A2-A1
Time taken is B2-B1
Speed is =(A2-A1)/(B2-B1) and the parentheses are needed to make sure
Excel
does the subtractions before the division

If the series of measurements are for a non-accelerating object maybe your
task (and this does sound like homework!) is to fit the data to a straight
line. Make a chart and add a trendline; then look in Help to lean about
the
SLOPE function

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Michael Koerner" wrote in message
...
I have sheet with columns listed as distance and time. An example of the
values in these columns are distance 5.0 km and the time column is
00:52:30.15 what type of formula would I would I need for a value in a
kilometre per hour column. TIA

--

Regards
Michael Koerner

Time, Distance, Speed
 

That is exactly what happened. thank you very much.

--

Regards
Michael Koerner


"Chuck" wrote in message
...
On Mon, 9 Mar 2009 12:30:30 -0400, "Michael Koerner"
wrote:

Chuck;

Thanks, will try it out.

When you try it and it doesn't work, the seconds should be divided by 3600
not
120.
--
Chuck

Time, Distance, Speed
 

On Mon, 9 Mar 2009 12:30:30 -0400, "Michael Koerner" wrote:

Chuck;

Thanks, will try it out.

When you try it and it doesn't work, the seconds should be divided by 3600 not
120.
--
Chuck

Time, Distance, Speed
 

Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Regards
Michael Koerner


"Chuck" wrote in message
...
On Mon, 9 Mar 2009 12:30:30 -0400, "Michael Koerner"
wrote:

Chuck;

Thanks, will try it out.

When you try it and it doesn't work, the seconds should be divided by 3600
not
120.
--
Chuck

Time, Distance, Speed
 

What are you trying to calculate?

If you are trying to calculate speed given a distance and a time then use:

=B3/(B4*24)

Where B3 is distance and B4 is time.


"Michael Koerner" wrote in message
...
Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Regards
Michael Koerner


"Chuck" wrote in message
...
On Mon, 9 Mar 2009 12:30:30 -0400, "Michael Koerner"
wrote:

Chuck;

Thanks, will try it out.

When you try it and it doesn't work, the seconds should be divided by 3600
not
120.
--
Chuck

Time, Distance, Speed
 

Will give that a try, thanks.

--

Regards
Michael Koerner


"Chuck" wrote in message
...
On Sat, 14 Mar 2009 13:19:44 -0400, "Michael Koerner"
wrote:

Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Regards
Michael Koerner


Sorry 'bout that. Try:
=C23/(LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600)
Note the parentheses after the / and after the 3600
--
Chuck

Time, Distance, Speed
 

On Sat, 14 Mar 2009 13:19:44 -0400, "Michael Koerner" wrote:

Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Regards
Michael Koerner


Sorry 'bout that. Try:
=C23/(LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600)
Note the parentheses after the / and after the 3600
--
Chuck

Time, Distance, Speed
 

Comments in-line:

"Chuck" wrote in message
...
On Sat, 14 Mar 2009 19:41:20 -0000, "Jellifish"
wrote:

What are you trying to calculate?

If you are trying to calculate speed given a distance and a time then use:

=B3/(B4*24)

Where B3 is distance and B4 is time.
00:53:23.44 is not a 'special' way to show days, which is what your
/(B4*24)
indicates.

Enter the time value in a cell and format as "hh:mm:ss.ss", you will see the
eading zeros.

Time is stored as a fraction of a day, so 0.5 will format as 12:00:00.00
(mid-day), that's why we need to multiply our time by 24, we want to
calculate distance per hour, not distance per day!

"Michael Koerner" wrote in message
...
Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Time is a funny number. Michael shows the time value as 00:53:23.44.
Gota be hh:mm:ss.ss. That is either a custom formatted cell or
formatted as
text. I don't know of any other way to show 00 hours. Also without a
second
time it must be elapsed time.

In any event:
if you divide 5.14 by 00:53:23.44 you get 138.6309717, wrong answer.



Did you try the formula I supplied?




correct answer is 5.776290488, however everything past the second second
decimal place is meaningless because seconds are only known to two decimal
places. Michael: nice timer.

You can write a procedure to change hh:mm:ss.ss to hours. The procedure
would
have the equation shown above. (with parentheses added as required)

--
Chuck

Time, Distance, Speed
 

On Sat, 14 Mar 2009 19:41:20 -0000, "Jellifish" wrote:

What are you trying to calculate?

If you are trying to calculate speed given a distance and a time then use:

=B3/(B4*24)

Where B3 is distance and B4 is time.
00:53:23.44 is not a 'special' way to show days, which is what your /(B4*24)
indicates.



"Michael Koerner" wrote in message
...
Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Time is a funny number. Michael shows the time value as 00:53:23.44.
Gota be hh:mm:ss.ss. That is either a custom formatted cell or formatted as
text. I don't know of any other way to show 00 hours. Also without a second
time it must be elapsed time.

In any event:
if you divide 5.14 by 00:53:23.44 you get 138.6309717, wrong answer.
correct answer is 5.776290488, however everything past the second second
decimal place is meaningless because seconds are only known to two decimal
places. Michael: nice timer.

You can write a procedure to change hh:mm:ss.ss to hours. The procedure would
have the equation shown above. (with parentheses added as required)

--
Chuck

Time, Distance, Speed
 

I have been using the formula =N(A2/(B2*24)) graciously provided by Bob
Phillips first reply in this thread which is working perfectly for me. Just
was trying all the others to see if they provided the same results, and to
see if I can't learn a little more about Excel.

Thanks to all

--

Regards
Michael Koerner


"Jellifish" wrote in message
...
Comments in-line:

"Chuck" wrote in message
...
On Sat, 14 Mar 2009 19:41:20 -0000, "Jellifish"
wrote:

What are you trying to calculate?

If you are trying to calculate speed given a distance and a time then use:

=B3/(B4*24)

Where B3 is distance and B4 is time.
00:53:23.44 is not a 'special' way to show days, which is what your
/(B4*24)
indicates.

Enter the time value in a cell and format as "hh:mm:ss.ss", you will see the
eading zeros.

Time is stored as a fraction of a day, so 0.5 will format as 12:00:00.00
(mid-day), that's why we need to multiply our time by 24, we want to
calculate distance per hour, not distance per day!

"Michael Koerner" wrote in message
...
Chuck tried your formula, and came up with a #DIV/0

C23 = 5.14
D23 = 00:53:23.44

Here is how I inserted it according to your earlier post.

=C23/LEFT(D23,2)+MID(D23,4,2)/60+MID(D23,7,5)/3600

--

Time is a funny number. Michael shows the time value as 00:53:23.44.
Gota be hh:mm:ss.ss. That is either a custom formatted cell or
formatted as
text. I don't know of any other way to show 00 hours. Also without a
second
time it must be elapsed time.

In any event:
if you divide 5.14 by 00:53:23.44 you get 138.6309717, wrong answer.



Did you try the formula I supplied?




correct answer is 5.776290488, however everything past the second second
decimal place is meaningless because seconds are only known to two decimal
places. Michael: nice timer.

You can write a procedure to change hh:mm:ss.ss to hours. The procedure
would
have the equation shown above. (with parentheses added as required)

--
Chuck

Time, Distance, Speed
 

No apology necessary Chuck, what is a discussion without different
viewpoints?

<g

"Chuck" wrote in message
...
On Sun, 15 Mar 2009 00:31:04 -0000, "Jellifish"
wrote:

I owe Jellyfish a huge apology.

Distance/(time * 24) does in fact give the correct answer.
When I tried it the answer looked weird and I left it at that. However if
I
had formatted answer cell to Number with 4 decimal places, I would have
seen
the result was correct. Like using the N function.

Chuck

Time, Distance, Speed
 

On Sun, 15 Mar 2009 08:12:25 -0400, "Michael Koerner" wrote:

I have been using the formula =N(A2/(B2*24)) graciously provided by Bob
Phillips first reply in this thread which is working perfectly for me. Just
was trying all the others to see if they provided the same results, and to
see if I can't learn a little more about Excel.

Thanks to all

Stick with Bob's equation.
--
Chuck

Time, Distance, Speed
 

On Sun, 15 Mar 2009 00:31:04 -0000, "Jellifish" wrote:

I owe Jellyfish a huge apology.

Distance/(time * 24) does in fact give the correct answer.
When I tried it the answer looked weird and I left it at that. However if I
had formatted answer cell to Number with 4 decimal places, I would have seen
the result was correct. Like using the N function.

Chuck

Comments in-line:

"Chuck" wrote in message
.. .
On Sat, 14 Mar 2009 19:41:20 -0000, "Jellifish"
wrote:

What are you trying to calculate?

If you are trying to calculate speed given a distance and a time then use:

=B3/(B4*24)

Where B3 is distance and B4 is time.

Time, Distance, Speed
 

On Sun, 15 Mar 2009 16:39:21 -0000, "Jellifish" wrote:

No apology necessary Chuck, what is a discussion without different
viewpoints?

Viewpoints, yes. Errors hmmmm.
--
Chuck

Time, Distance, Speed
 

Ok then, how about this:

If you never make mistakes, then you are not trying hard enough!

:o)

"Chuck" wrote in message
...
On Sun, 15 Mar 2009 16:39:21 -0000, "Jellifish"
wrote:

No apology necessary Chuck, what is a discussion without different
viewpoints?

Viewpoints, yes. Errors hmmmm.
--
Chuck