Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

"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!

Check out our Excel Resources

Re: "average speed" calculation

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

Re: "average speed" calculation

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.

Re: "average speed" calculation

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

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.