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

 

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

 

[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

 

[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?

 

[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>

 

[Jellifish]

Ok then, how about this:

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

)