Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: IF function and times

  1. #1
    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 All,

    Been handed this project and a continuation from yesterday's question.

    Column A and B are scheduled arr/dep time
    Column C and D are actual arr/dep times
    Column E is aircraft type
    I need to count 1 if:
    d-c is less than or equal to 30 mins
    if c is less than a, and d-c is less than or equal b-a
    and if aircraft type is 717 (column e)

    I have this formula, but it's the opposite of what I intended.

    IF(OR(F6="",G6=""),"",IF((F6-G6)*1440>VLOOKUP(B6,Lookup,3,0),1,0))


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian
    Someone will probably give you a good formula to use, if not I'll write you a macro. Am kinda new at the Excel thing.
    Learning a lot here though.
    Good Luck
    Tom

  3. #3
    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

    Brian,

    > I need to count 1 if:
    > StatementA d-c is less than or equal to 30 mins
    > StatementB if c is less than a,
    > StatementC and d-c is less than or equal b-a
    > StatementD and if aircraft type is 717 (column e)

    is ambigious:

    Must StatementA, StatementB, and StatementC all be TRUE at the same time [AND all]

    or

    one of these being TRUE is sufficient [OR]?

    Please disregard StatementD in answering the preceeding question.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-26 13:18 ]

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,427
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    try

    =IF(COUNT(A3:E3)=5,AND(D3-C3<=I1,C3

  5. #5
    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,

    I knew this was going to be tough to explain

    I have a table with aircraft and ground time parameters, hence the vlookup in formula.

    If a 717 aircraft has a scheduled ground time of 45 mins (b-a). It arrives late (c>a). It leaves late (d>b). However, it spends >30 mins and <45 mins on the ground (d-c), count as 1.
    A 767 aircraft is allowed 45 mins
    A DC10 is allowed 75 mins.
    I'm getting confused!

  6. #6
    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 13:37, Brian from Maui wrote:
    Aladin,

    I knew this was going to be tough to explain

    I have a table with aircraft and ground time parameters, hence the vlookup in formula.

    If a 717 aircraft has a scheduled ground time of 45 mins (b-a). It arrives late (c>a). It leaves late (d>b). However, it spends >30 mins and <45 mins on the ground (d-c), count as 1.
    A 767 aircraft is allowed 45 mins
    A DC10 is allowed 75 mins.
    I'm getting confused!
    Thus, when JSF is scheduled to depart at 06:00 and to arrive/ground at 06:04, and the lookup table says 4 minutes, and when it departs at 12:00 and grounds at 12:02, we count this event as 1. Is that right?


  7. #7
    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

    Here's an example:

    A 7:00
    B 7:45
    C 7:20
    D 7:51
    Count as 1, because although it's alloted 45(b-a) mins, it exceed 30 mins (d-c) and because it's a 717 aircraft count as 1.

    table:
    717 31
    767 46
    D10 75
    I have another column between both, hence 3 on the syntax. and a cell to reference what type of aircraft.

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian

    Sorry to step in .. Aladins your man can i suggest you ask if poss can you sent Aladin the WkBk if thats at all poss, of cause if Aladin does not mind, not my place to say its ok, must be worth the asking of me thinks:

    ////


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  9. #9
    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 13:57, Brian from Maui wrote:
    Here's an example:

    A 7:00
    B 7:45
    C 7:20
    D 7:51
    Count as 1, because although it's alloted 45(b-a) mins, it exceed 30 mins (d-c) and because it's a 717 aircraft count as 1.

    table:
    717 31
    767 46
    D10 75
    I have another column between both, hence 3 on the syntax. and a cell to reference what type of aircraft.
    I'm trying to get you to formulate the rules when a flight event counts as a hit (that's, 1) for a given aircraft. I'll harass you until a consistent set of rules is settled/negotiated .

    Here the list we have so far:

    [1] The central object is the elapsed time: that's the time spent between departure and grounding.
    [2] We have for each flight event an allotted (scheduled) duration: b-a [that is, the value in B minus the value in A].
    [3] We have for each flight event an actual duration: d-c [that is, the value in D minus the value in C].
    [4] We have for each flight event the type of the aircraft involved: e [that is, the value in E].
    [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).

    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

    otherwise 0.

    Please read this carefully. Make suggestions/corrections using the terms deined here. If needed, add new terms along with their definitions.

    Am I getting close?

    Aladin


  10. #10
    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-26 15:10, Aladin Akyurek wrote:
    On 2002-03-26 13:57, Brian from Maui wrote:
    Here's an example:

    A 7:00
    B 7:45
    C 7:20
    D 7:51
    Count as 1, because although it's alloted 45(b-a) mins, it exceed 30 mins (d-c) and because it's a 717 aircraft count as 1.

    table:
    717 31
    767 46
    D10 75
    I have another column between both, hence 3 on the syntax. and a cell to reference what type of aircraft.
    I'm trying to get you to formulate the rules when a flight event counts as a hit (that's, 1) for a given aircraft. I'll harass you until a consistent set of rules is settled/negotiated .

    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.

    [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.

    [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

    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

    otherwise 0.

    Please read this carefully. Make suggestions/corrections using the terms deined here. If needed, add new terms along with their definitions.

    Am I getting close?

    YES!

    Aladin


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
  •