Time, Distance, Speed

[Michael Koerner]

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

 

[Bob Phillips]

=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)

 

[Bernard Liengme]

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

 

[Michael Koerner]

Bob;

It does, and thank you very much

--

Regards
Michael Koerner


=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]

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

--

Regards
Michael Koerner


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

 

[Michael Koerner]

Chuck;

Thanks, will try it out.
--

Regards
Michael Koerner

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

 

[Bernard Liengme]

Sorry. I just started to seventh decade last month!

 

[Michael Koerner]

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


Sorry. I just started to seventh decade last month!

 

[Michael Koerner]

That is exactly what happened. thank you very much.

--

Regards
Michael Koerner

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]

Chuck;

Thanks, will try it out.

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

 

[Michael Koerner]

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;

Thanks, will try it out.

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

 

[Jellifish]

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]

Will give that a try, thanks.

--

Regards
Michael Koerner

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

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]

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

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

 

[Jellifish]

Comments in-line:

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!

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?

 

[Chuck]

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.
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)

 

[Michael Koerner]

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


Comments in-line:

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!

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?

 

[Jellifish]

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

<g>

 

[Chuck]

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]

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