Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate average speed
What is the corecct / simple formular to calculate the average speed?
In one colum I enter the distance (miles) traveled. The other colum I enter the time. Time format is "hh:mm" What I would like is to have the third colum look at both of these columns and based on a formular?? display the average speed. I have attempted this by trying the "CONVERT" function to convert the time entered to all minutes but for some reason it does not work correctly?? For example the time entered is 3:05 which is 185 minutes. When I use the "CONVERT" function it comes out as 183 ??? Is the problem with the time format that I'm using? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate average speed
41db14 Wrote: What is the corecct / simple formular to calculate the average speed? In one colum I enter the distance (miles) traveled. The other colum I enter the time. Time format is "hh:mm" What I would like is to have the third colum look at both of these columns and based on a formular?? display the average speed. I have attempted this by trying the "CONVERT" function to convert the time entered to all minutes but for some reason it does not work correctly?? For example the time entered is 3:05 which is 185 minutes. When I use the "CONVERT" function it comes out as 183 ??? Is the problem with the time format that I'm using? Try this...assume A1~C1 are Mile, Time, and Speed respectively. Make sure time is input in text format so the formula bar displays "3:05" not "3:05:00 AM". C2 = A2/(CONVERT(LEFT(B2,FIND(":",B2)-1),"hr","mn")+RIGHT(B2,LEN(B2)-FIND(":",B2))) -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=522723 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate average speed
"41db14" wrote in message ... What is the corecct / simple formular to calculate the average speed? In one colum I enter the distance (miles) traveled. The other colum I enter the time. Time format is "hh:mm" What I would like is to have the third colum look at both of these columns and based on a formular?? display the average speed. I have attempted this by trying the "CONVERT" function to convert the time entered to all minutes but for some reason it does not work correctly?? For example the time entered is 3:05 which is 185 minutes. When I use the "CONVERT" function it comes out as 183 ??? Is the problem with the time format that I'm using? Format time cell as [h]:mm:ss Avg Speed is distance over time, so If A1 is time, and B1 is distance, C1 is =B1/(A1*24) Beege |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate average speed
"Beege" wrote in message
... "41db14" wrote in message ... What is the corecct / simple formular to calculate the average speed? In one colum I enter the distance (miles) traveled. The other colum I enter the time. Time format is "hh:mm" What I would like is to have the third colum look at both of these columns and based on a formular?? display the average speed. I have attempted this by trying the "CONVERT" function to convert the time entered to all minutes but for some reason it does not work correctly?? For example the time entered is 3:05 which is 185 minutes. When I use the "CONVERT" function it comes out as 183 ??? Is the problem with the time format that I'm using? Format time cell as [h]:mm:ss Avg Speed is distance over time, so If A1 is time, and B1 is distance, C1 is =B1/(A1*24) Beege And converting Hours to Minutes is =A1*1440 Beege |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate average from large dataset in several sheets? | Excel Worksheet Functions | |||
Calculate the average using the Lookup function or similar | Excel Discussion (Misc queries) | |||
calculate average in percentage coloumn with #DIV/! (ignore error | Excel Discussion (Misc queries) | |||
calculate average hours and minutes | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions |