IF function and times

[Brian from Maui]

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))

[Tom Schreiner]

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

[Aladin Akyurek]

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 ]

[Dave Patton]

try

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

[Brian from Maui]

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!

[Aladin Akyurek]

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?

[Brian from Maui]

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.

[Jack in the UK]

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:

////

[Aladin Akyurek]

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

[Brian from Maui]

On 2002-03-26 15:10, Aladin Akyurek wrote:

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