Thanks:  0
Likes:  0

# Thread: IF function and times

1. Here the list we have so far:

[1]The central object is the elapsed time: that's the time spent between departure and grounding.

YES

[2] We have for each flight event an allotted (scheduled) duration: b-a [that is, the value in B minus the value in A].

YES, however, if the flight is late, the allotted time is then deferred to the type of aircraft.

I don't think I understand the qualification coming right after "YES".

[3] We have for each flight event an actual duration: d-c [that is, the value in D minus the value in C].

YES, again the duration if the flight is late (c>a) the allotted time is then deferred to the type of aircraft.

The same trouble as under [2].

[4] We have for each flight event the type of the aircraft involved: e [that is, the value in E].

YES

[5] We have a norm table that, for each type of aircraft, shows the allowed duration (the norm) that it needs to take for a flight (apparently for a fixed, single type of traject).

A lookup table when a certain aircraft is entered in a cell

That's what I meant: a lookup table of AllowedDurations for each type of aircraft, as Barrie suggested.

The data area in the relevant worksheet consists of columns A:E where A houses the scheduled departure time, B scheduled arrival/grounding time, C the actual departure time, D the actual arrival/grounding time, and E the type of aircraft.

In F, we want to see a 1 for the aircraft in E, when:

ActualDuration<=AllowedDuration OR
ActualDuration<=AllotedDuration

Actual Duration<=AllowedDuration by aircraft type (717 31 mins, 767, 45 mins) and b-a
Actual duration<=Allotted duration IF C<=A

The "IF C<=A" bits means that the aircraft must leave at least on time. OK.

otherwise 0.

Am I getting close?

YES! [img]/board/images/smiles/icon_biggrin.gif[/img]

In F2 enter and copy down as far as needed:

=(C2<=A2)*((D2-C2)*1440<=VLOOKUP(E2,NormTable,2,0)+(D2-C2<=B2-A2))

Aladin

2. Aladin,

MAHALO!

I'll do some extensive testing tonight. Get some rest! I have a million projects now! So much work and so little time!

Brian

3. Aladin,

It does not count this scenario
A 7:00
B 7:45
C 7:15
D 7:45
This should count as 1, because (I left this out) it did not exceed schedule dep time (D=B) and did not exceed 30 mins.

4. On 2002-03-26 16:37, Brian from Maui wrote:
Aladin,

It does not count this scenario
A 7:00
B 7:45
C 7:15
D 7:45
This should count as 1, because (I left this out) it did not exceed schedule dep time (D=B) and did not exceed 30 mins.
D=B ?

Try:

=((((C2<=A2)+(D2=B2))*((D2-C2)*1440<=VLOOKUP(E2,NormTable,2,0)+(D2-C2<=B2-A2))>0)+0

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-26 17:15 ]

5. Aladin,

Here in Hawaii we say MAHALO! and

YOU DA MAN!!!

And you too Barrie!

Brian

[ This Message was edited by: Brian from Maui on 2002-03-26 18:57 ]

6. Aloha Again!

After testing the formula, I found this scenario.

Aircraft 580 has a 46 mins allotted time, if C>A and D>B, even if B-A is greater than 46 mins.

A 8:00
B 10:15
C 9:35
D 10:20

Should count as 1 because D-C is less than 46 mins.

Anybody else confused besides me?

7. On 2002-03-27 11:06, Brian from Maui wrote:
Aloha Again!

After testing the formula, I found this scenario.

Aircraft 580 has a 46 mins allotted time, if C>A and D>B, even if B-A is greater than 46 mins.

A 8:00
B 10:15
C 9:35
D 10:20

Should count as 1 because D-C is less than 46 mins.

Anybody else confused besides me?
Scheduled Actual Aircraft
7:00 7:30 7:01 7:33 717
7:00 7:45 7:45 7:45 717
8:00 10:15 9:35 10:20 767
7:00 7:30 7:00 7:30 767
7:00 7:30 7:00 7:31 767
7:40 8:20 7:40 8:25 D10

The above is in A1:E7. The same data in more usable form:

{"Scheduled","","Actual","","Aircraft";
0.291666666666667,0.3125,0.292361111111111,0.314583333333333,717;
0.291666666666667,0.322916666666667,0.322916666666667,0.322916666666667,717;
0.333333333333333,0.427083333333333,0.399305555555556,0.430555555555556,767;
0.291666666666667,0.3125,0.291666666666667,0.3125,767;
0.291666666666667,0.3125,0.291666666666667,0.313194444444444,767;
0.319444444444444,0.347222222222222,0.319444444444444,0.350694444444444,"D10"}

The lookup table (named NormTable) is:

{"Aircraft","Mins";
"D10",76;
717,31;
767,46}

What are the expected results that you should get in F from F2 on?

Aladin

8. On 2002-03-27 11:47, Aladin Akyurek wrote:
On 2002-03-27 11:06, Brian from Maui wrote:
Aloha Again!

After testing the formula, I found this scenario.

Aircraft 580 has a 46 mins allotted time, if C>A and D>B, even if B-A is greater than 46 mins.

A 8:00
B 10:15
C 9:35
D 10:20

Should count as 1 because D-C is less than 46 mins.

Anybody else confused besides me?
Scheduled Actual Aircraft
7:00 7:30 7:01 7:33 717 = 0 because 7:33-7:01 > 7:30-7:00
7:00 7:45 7:45 7:45 717 = 1
because 7:45-7:45 < 7:45-7:00
8:00 10:15 9:35 10:20 767 = 1
because 10:20-9:35 < 10:15-8:00
7:00 7:30 7:00 7:30 767 = 1
because 7:30-7:00 <=7:30-7:00
7:00 7:30 7:00 7:31 767 = 0
because 7:31-7:00 > 7:30-7:00
7:40 8:20 7:40 8:25 D10 = 0
because 8:25-7:40 > 8:20-7:40

The mins for aircraft is applied when:

8:00 10:15 9:35 10:25 767 = 0
because 10:25-9:35 > 46,
although 10:25-9:35 < 10:15-8:00

The above is in A1:E7. The same data in more usable form:

{"Scheduled","","Actual","","Aircraft";
0.291666666666667,0.3125,0.292361111111111,0.314583333333333,717;
0.291666666666667,0.322916666666667,0.322916666666667,0.322916666666667,717;
0.333333333333333,0.427083333333333,0.399305555555556,0.430555555555556,767;
0.291666666666667,0.3125,0.291666666666667,0.3125,767;
0.291666666666667,0.3125,0.291666666666667,0.313194444444444,767;
0.319444444444444,0.347222222222222,0.319444444444444,0.350694444444444,"D10"}

The lookup table (named NormTable) is:

{"Aircraft","Mins";
"D10",76;
717,31;
767,46}

What are the expected results that you should get in F from F2 on?

Aladin

9. Hi Brian,

What follows is a new formula that attempts to accomodate the modifications to the rules discussed earlier:

=IF((D2-C2<=B2-A2)*(ROUND((D2-C2)*1440,0)<=VLOOKUP(E2,NormTable,2,0)),1,IF(D2<=B2,1,0))

Note. Instead of looking up E2 in NormTable, you can enter directly in E2 the relevant, retrievable value. In that case, the formula should be changed to:

=IF((D2-C2<=B2-A2)*(ROUND((D2-C2)*1440,0)<=E2),1,IF(D2<=B2,1,0))

Please carry out more tests before adopting it.

Aladin

10. Aladin,

My mistake, didn't see it!

Again MAHALO!!!

Brian

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•