calculate average speed with a start and end time

June 6, 2007 7:18 PM

Excel Filter: How do I calculate average speed with a start and end time?

Say we have cells that look like this:

A1 -> Start Time (entered like 8:00 AM)
B1 -> End Time (entered like 8:34 AM)
C1 -> Elapsed Time =B1 - A1 (Displays number of minutes (the nature of the data is that most times will be less than an hour))
D1 -> Distance (entered like "5" in miles)
E1 -> Avg Speed =D1 / ( C1 / 60) (in miles per hour)

The problem is E1 doesn't give me the Avg Speed, it gives me some strange answer. I know it is related to C1 being formatted as time. How do I fix this?

posted by bytewrite to computers & internet (7 comments total)

E1 should contain something like:

=D1/(24 * (B1-A1))

And the format the cell as general, or something numeric. Excel will try to be smart and make it a time.

The "24" is because for times and dates, the unit is 1 day.
posted by buxtonbluecat at 7:29 PM on June 6, 2007 [1 favorite]

Or try "=MINUTE(b1-a1)" in c1
posted by clarahamster at 7:30 PM on June 6, 2007

What are the exact values of A1 and B2 you're using and what formula are you using in cell C1? I'm guessing C1's result in conjunction with D1 is fubaring the E1 calculation.
posted by jmd82 at 7:34 PM on June 6, 2007

Daw' gone preview. clarahamster's got the right idea I think.
posted by jmd82 at 7:35 PM on June 6, 2007

google is your friend...
posted by twiggy at 7:41 PM on June 6, 2007

=MINUTE(b1-a1)

...will break if, say, the times are 8:00am and 10:34am - it will return 34. Which is not what the poster wants.

The way I suggested works. Really, truly, it does.
posted by buxtonbluecat at 7:42 PM on June 6, 2007

He is right it does. Thanks buxtonbluecat.
posted by bytewrite at 7:54 PM on June 6, 2007