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))
 
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! /board/images/smiles/icon_biggrin.gif


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
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Aladin,

Here in Hawaii we say MAHALO! and

YOU DA MAN!!!

:biggrin:

And you too Barrie! :biggrin:

Brian
This message was edited by Brian from Maui on 2002-03-26 18:57
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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