IF function and times

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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))
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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
 
Upvote 0
try

=IF(COUNT(A3:E3)=5,AND(D3-C3<=I1,C3<A3,AND(D3-C3<=B3-A3))*1,0)
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:

////
 
Upvote 0
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 :biggrin:.

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
 
Upvote 0
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 :biggrin:.

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! :biggrin:

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top