IF function and times - Page 2
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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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,445
    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,445
    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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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,445
    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,445
    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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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,445
    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
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 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,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Aladin,

    My mistake, didn't see it!



    Again MAHALO!!!

    Brian

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
  •  

 

 
DMCA.com