"average speed" calculation

Posted by tom womack on January 10, 2002 1:43 PM

i'm trying to get my excel to calculate average "mph" denoted as a number with 1 decimal place. excel doesnt like mixing formats...so i'm having difficulty. i want to calculate an average speed, based on tabulated miles driven and tabulated time driven (in [h]:mm) format...HELP!

Posted by Barrie Davidson on January 10, 2002 1:49 PM

Try this formula (assumes miles are in cell A1 and time is in cell B1)

=A1/B1/24

You'll have to format this so it does not appear in time format.

Regards,
BarrieBarrie Davidson

Posted by IML on January 10, 2002 1:49 PM

try
=A1/(B1*24)
formatted as number to 1 place.

A1 is distance, B1 is time.

Posted by Damon Ostrander on January 10, 2002 1:56 PM

Hi Tom,

If you have entered the time data in the cell in time-date format (i.e., formatting the cell as "Time" in [h]:mm) rather than inputting it as a text string, then the underlying value of the cell is actually in days and you must treat it accordingly. As a result the value for mph should be the miles cell divided by the hours cell, and then divided by 24.

Damon