Complicated Time Formula

[JM]

Hello,

This is a bicycle ride log, and I am having a really hard time with some
time formulas. I'm trying to calculate three metrics (denoted by ?) based
on the three data points that my bike speedometer provides (please see table
below).


Miles 27

+Ride Time ?
+Rest Time ?
=Total Time 2:15:49

Total Time Avg Speed ?
Ride Time Avg Speed 12.4

Can anyone help? I sort of calculated the total time with
=C8/(HOUR(C12)+MINUTE(C12)/60+(IF(SECOND(C12)>30,1/60,0))), but I didn't
handle the seconds very well. I couldn't figure out how to add the seconds
in, so I just rounded the minute.

Thanks! JM

 

[Peo Sjoblom]

Multiply the times with 24 that will give you decimal time, what average
time are you looking for
average miles per hour for total time is

=27/("02:15:49"*24)

11.93 m/h

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[JM]

Yep, that helps a lot. Thanks!

BTW, What is the ">" symbol next to all the From names? Does that mean
posted through the MS site (vs usenet direct)?

Thanks! JM

 

[Ron Rosenfeld]

Hello,

This is a bicycle ride log, and I am having a really hard time with some
time formulas. I'm trying to calculate three metrics (denoted by ?) based
on the three data points that my bike speedometer provides (please see table
below).


Miles 27

+Ride Time ?
+Rest Time ?
=Total Time 2:15:49

Total Time Avg Speed ?
Ride Time Avg Speed 12.4

Can anyone help? I sort of calculated the total time with
=C8/(HOUR(C12)+MINUTE(C12)/60+(IF(SECOND(C12)>30,1/60,0))), but I didn't
handle the seconds very well. I couldn't figure out how to add the seconds
in, so I just rounded the minute.

Thanks! JM

Is this a school assignment?

Excel stores times as fractions of a day. So you should be able to use that
fact to derive the formulas you need.


--ron