Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: IF function and times

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #12
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #13
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #15
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #16
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #17
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #18
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #19
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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. #20
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    My mistake, didn't see it!



    Again MAHALO!!!

    Brian

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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