Calculating Time from Speed and Distance

Hi everyone,

I am trying to find the time from the given set of values.

b1 - starting distance c1 - end distance(Km) d1 - speed(Kmph) e1 - time
B C D E
1 0.00 1.25 24 00:03:08
2 1.25 1.75 36 00:00:50
3 1.75 3.10 18 00:04:30
4 3.10 3.90 28 00:01:43
5 3.90 5.00 32 00:02:04

The column E contains this formula
(C1-B1)/(D1*24) and it displays the time as 00:03:08

Now I have set of distances and I need to find the time.

Eg:

H1 = 1.20 in I1 I get 0:03:00
H2 = 1.25 but in I2 I get 00:00:05

This answer what I get in I2 is wrong.
In I2 the answer should be 00:00:08 Seconds.
Because both the values lies inbetween the same values of C1 but it is calculating the time from the next value.
If the query value H1 (Distance) is not in C1 then it should add the time from E1 and then do the calculation and display the result.
I tried using this formula
(H2-B2)/((D2:D6)*24) but it gave correct value for the 1st value.
Rest all I am getting wrong answers.

The following are the values in H1:H5

H I
1.20 00:03:00
1.24 00:00:06
3.23 00:05:38
4.44 00:02:27
4.96 00:00:58

Am only getting I1 as correct answer. The values in Column are correct because I did the calculation work manually.

Could somebody help me in solving this problem.

File attached with explanations.

Hope that will help.

I follow what you are asking, but do you want a macro to do this? Or just formulas?

Anything which makes my job easier will be helpful.

Here's a formula-based solution. I'm sure there's a better way to do this but formulas are not my forte.

time calculation.xlsx

Originally Posted by AlphaFrog

Here's a formula-based solution. I'm sure there's a better way to do this but formulas are not my forte.

Attachment 237149

You are the man AlphaFrog.

This is what am looking for.
This forum really rocks.
I can able to learn more things from this.