Conditional Time Calculation

[naga rajan]

Hi everyone,

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

b1 - starting distance c1 - end distance d1 - speed 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.
I tried using this formula
(H2-B2)/((D26)*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.

Regards,
Naga

 

[GS]

Does this work as expected?

In I1...

=(H1-B1)/(D1*24)

...and copy down.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[naga rajan]

Hi Gary,

This is not working.
Suppose if the query value(H1) is greater than C1 then it should calculate the time from 2nd Row and so on.

 

[GS]

Hi Gary,

This is not working.
Suppose if the query value(H1) is greater than C1 then it should
calculate the time from 2nd Row and so on.

Yes, I see that now. Thanks for pointing that out!

Have a look at the OFFSET() function to see if this gets you pointed in
the right direction.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[naga rajan]

Hi Gary,

This Offset() is not helping me.
Some combinations of IF and WHILE statements will help I think.

 

[GS]

Hi Gary,

This Offset() is not helping me.
Some combinations of IF and WHILE statements will help I think.

Use OFFSET() in your IF()s to ref the value in the next row when
criteria dictates.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[naga rajan]

Another example.
I think the first one is confusing more.

FROM TO SPEED TIME
0.00 0.60 30.00 00:01:12
0.60 1.15 24.00 00:01:22
1.15 1.80 36.00 00:01:05
1.80 2.30 18.00 00:01:40
2.30 3.00 45.00 00:00:56

Here the answer for value 0.50 is 00:01:00
But for 0.60 the answer should be 00:00:12.
But it calculates the time from the speed of 24.
0.60 also present in the same section of 0.00 to 0.60.
But why it is calculating the time from the next cell?
Is it because the query value is present in the corresponding row?
I tried this formula but no use.

(h3-h2)/((d2:d6)*24)

DISTANCE TIME
0.50 00:01:00
0.60 00:00:15

Any idea for it?

 

[GS]

Check these calcs...

Start Stop Speed Time 30 24 36 18 45
0.00 0.60 30 0:01:12 0:01:12 0:01:30 0:01:00 0:02:00 0:00:48
0.60 1.15 24 0:01:22 0:01:06 0:01:22 0:00:55 0:01:50 0:00:44
1.15 1.80 36 0:01:05 0:01:18 0:01:38 0:01:05 0:02:10 0:00:52
1.80 2.30 18 0:01:40 0:01:00 0:01:15 0:00:50 0:01:40 0:00:40
2.30 3.00 45 0:00:56 0:01:24 0:01:45 0:01:10 0:02:20 0:00:56

...where each distance is calc'd for each speed.

As per your statements, if distance 0.50 takes 1:00 at speed 30
(Row4,Col5) then it's feasible that distance 0.60 (20% further) takes
1:12 (20% longer) at the same speed (Row1,Col5).

The concept of your formula is basically...

=Distance/(Speed*24)

...where it assumes speed is distance per hour.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi,

Am Tue, 9 Apr 2013 22:56:37 -0700 (PDT) schrieb naga rajan:

FROM TO SPEED TIME
0.00 0.60 30.00 00:01:12
0.60 1.15 24.00 00:01:22
1.15 1.80 36.00 00:01:05
1.80 2.30 18.00 00:01:40
2.30 3.00 45.00 00:00:56

where does your speed data come from? Most speedometers show a wrong
speed. So it is better to stop the time and calculate the speed.


Regards
Claus Busch

 

[GS]

Hi,

Am Tue, 9 Apr 2013 22:56:37 -0700 (PDT) schrieb naga rajan:


where does your speed data come from? Most speedometers show a wrong
speed. So it is better to stop the time and calculate the speed.


Regards
Claus Busch

This OP is clocking distance at speed. What we don't know is the
calibration of speed (ie: miles/kms per hours; feet per second; etc.)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[naga rajan]

The speed and distance is entered by the user.
The distances are in Km and Speeds are in Kmph.
We want to get the answer (Time) for the queried distance from the given set of values.
If the queried value is present in next row it should add the time from the previous row to that result.