Need your help please , complex issue

[excellover]

Here is my ordeal,

I want excel to calculate the correct earliest arrival time to my fina
destination stopping at a gas station and continuing with the remainin
distance/speed/speed correction and 1 hour margin as follows,

i) Start time is = 22:00 ( in cell B2 , and formatted it as time, hh:m
)

Time to the gas station = 4:30 hours (in cell B3, and formatted it a
time, hh:mm )

Arrival time to gas station = 02:30am ( in cell B8 , excel calculate
correct time, as the cell B8 was also formatted as time, hh:mm)

Now here is my problem with combination of distance/speed and time t
final destination

ii) In Cell B4 I have Distance remaining to destination = 350 miles
In Cell B5 I have the speed of the car per hour = 175 miles/hour
In Cell B6 I have the expected excess speed of 39 miles (this wil
increase my speed of 175 miles/hour by 39 miles; this is not a fixe
value but may change depending on many factors).
Finally I want to further subtract 1 hour margin.

On a normal calculator I would do the math as follows
i )

22:00 + 4:30 = 02:30am (this is the initial arrival time to ga
station)

ii )

350 / ( 175+39 ) -1 hour , the result will be as flows
350 / (214) = 1.63 (1 h 37 mnts (.63 *60 is actually 37 minutes))
1h 37 mnts is the time required to travel the 350 miles.

Final result:
2:30am + 1:37 = 4:07am (- 1 hour margin) = 03:07
Expected earliest time of arrival to destination therefore is 03:0
am.

iii) How can I put them all in one formula?

I would very much appreciate your help in this one , as I am using i
on my job to calculate the times and I have strong believe in excel th
power it has on those formulas.

Thank you.
Email = (e-mail address removed)




:

 

[Biff]

Hi!

Try this and format the cell as TIME:

=((B2+B3)+(B4/(B5+B6)/24))-1/24

The answer to this is: 3:08 AM

The 1 minute difference is due to a rounding issue!

Biff

 

[excellover]

Biff , Thank You very much ,
This formulla is working just fine , how is the 24 came to play?
never thought about it

 

[Biff]

Hi!

24 represents the number of hours in a day.

350/(175+39) returns a decimal value of: 1.635514019 which
represents hours. You then need to determine what amount
of a day (24 hours) that is. So, 1.6355...hours equals
0.068146417 of a day.

Since you also wanted to subtract 1 hour from the total
you have to determine what fraction of a day is 1 hour. So:

1/24 = 0.041666667 of a day.

The rounding issue that I mentioned occurred with the
value of 350/(175+39) = 1.64 vs 1.6355....

You can eliminate that by using this formula:

=((B2+B3)+(TRUNC(B4/(B5+B6),2)/24))-1/24

But, does that 1 minute difference really matter in this
situation?

I would really like to know how you drive 175 mph? Is this
possibly for a rally type race?

Biff

 

[excellover]

Hi,
Thank you for the explanation, the one minute doesn't really affect th
time process. As for the 175mph speed, I was just taking the worst cas
scenario and for the purpose of adjusting the formula as such