Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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)/((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. Regards, Naga |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
Hi Gary,
This Offset() is not helping me. Some combinations of IF and WHILE statements will help I think. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Time Calculation
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop time - start time calculation | Excel Worksheet Functions | |||
Time calculation (Subraction of Idle Time) | Excel Discussion (Misc queries) | |||
Conditional calculation? | Excel Worksheet Functions | |||
Ignoring Time in a Date Time Calculation | Excel Worksheet Functions | |||
time calculation with military time | Excel Worksheet Functions |