Question on LOGIC ?? - PLEASE HELP

[PM]

I have the following worksheet setup:
Column A = Origin Column C = Destination Column E = Airline
Cell A2: DTW Cell C2: IAH Cell E2: CO
Cell A3: DTW Cell C3: IAH Cell E3: NW
Cell A4: DTW Cell C4: MCO Cell E4: AA
............
.........
...........
18,900 rows follow with similar data

My objective is three fold:

1.
cell E2 = CO, and Cell A2 or Cell C2 contains origin/destination as
DTW, or MEM, or MSP, I would like the a cell on the sheet i.e. Cell F2
= 0

2.
If cell E3 = NW, and Cell A3 or Cell C3 contains origin/destination as
DTW, or MEM, or MSP, I would like the a cell on the sheet i.e. Cell F3
= 1

3.
Finally, if cell E4 = AA, I would like the a cell on the sheet i.e.
Cell F4 = 1

I would like to present an example of what I have done so far to
achieve my objective. Let us take Row 2 as an example where I placed 1
formula in Cell L2:

=IF(E2="NW","NW",IF(OR(E2="CO"),"CO","OTHER"))

and;
another in Cell M3:

=IF(E2="NW",OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),IF(OR(E2="CO"),OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW",C2="MEM",C2="MSP"),"OTHER"))

The result is completely incoherent. I have been unsuccessful and need
some help with the logic and possibly a new formula. If you could
explain a methodology to achieve my desired outcome, and modify my
formulas, or give me a formula, I would appreciate it.

Thank you Excel gurus...........

 

[Eric]

Try this formula (all in the same cell).


=IF((OR(A2="DTW",A2="MEM",A2="MSP",C2="DTW"
,C2="MEM",C2="MSP")*(OR(E2="CO",E2="NW")))=1,0,IF
(E2="AA",1,""))

It gave me the result I think you were searching for.


Eric

 

[Mike]

If I read your query correctly, I think the formula that you want is as
follows:

=IF(AND(E1="CO",OR(A1="DTW",A1="MEM",A1="MSP"),OR(C1="DTW",C1="MEM",C1="MSP"
)),0,IF(AND(OR(E1="NW",E1="AA"),OR(A1="DTW",A1="MEM",A1="MSP"),OR(C1="DTW",C
1="MEM",C1="MSP")),1,"OTHER"))

Is this what you were after?

Mike

 

[Mike]

Reading your query again, I think what you want is actually as follows:

=IF(AND(E1="CO",OR(A1="DTW",A1="MEM",A1="MSP",C1="DTW",C1="MEM",C1="MSP")),0
,IF(AND(E1="NW",OR(A1="DTW",A1="MEM",A1="MSP",C1="DTW",C1="MEM",C1="MSP")),1
,IF(E1="AA",1,"OTHER")))

Apologies if previous posting was misleading

Mike

 

[PM]

Weve come close with Mike and Eric's help, but we need to make one
small correction, your help is again appreciated.

Let me clarify it this time......
TABLE FROM A2:e8

COLUMN A B C D E
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Using this formula =IF((OR(A5="DTW",A5="MEM",A5="MSP",B5="DTW",B5="MEM",B5="MSP")*(OR(C5="CO",C5="NW")))=0,1,IF(C5<>"NW",IF(C5<>"CO",1,"0"),"1"))

in COLUMN D (NOW) produces the results we want with one exception: Row
with DTW, IAH, and NW should give us 1 (as shown in column E), not 1.
Im sure its an easy fix, could you please help.

thanks a million




--------------------------------------------------

 

[Vasant Nanavati]

Did you see my suggested solution in the .programming section? Please don't
mulitpost!

--

Vasant

Weve come close with Mike and Eric's help, but we need to make one
small correction, your help is again appreciated.

Let me clarify it this time......
TABLE FROM A2:e8

COLUMN A B C D E
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Using this formula

=IF((OR(A5="DTW",A5="MEM",A5="MSP",B5="DTW",B5="MEM",B5="MSP")*(OR(C5="CO",C

 

[PM]

Once again, after your formulas, I think I can now clarify exactly
what I desire from this effort:

Table A2:E8
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Desired Column Conditions
Keep (desired value=1) all NW flights with DTW, MEM, or MSP in
origin/destin
Remove (desired value=0) all other NW flights
Remove (desired value=0) all CO flights with DTW, MEM, or MSP in
origin/destin
Keep (desired value=1) all other CO flights
Keep (desired value=1) all other airline flights

Formula in D3 at this time:
=IF((OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP")*(OR(C3="CO",C3="NW")))=1,0,IF(C3<>"CO",IF(C3<>"NW",1,"0"),"1"))

This works for all conditions except the row with DTW, IAH, NW - which
gives me gives a value of 0, when the conditions need it to be =1
For reference puposes, the correct values are presented in Column E,
labeled the "desired" column.

Please help me out on this one, thanks

 

[PM]

I think I can clarify exactly what I desire from my nested if effort:

Table A2:E8
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Desired Column Conditions
1. Keep (desired value=1) all NW flights with DTW, or MEM, or MSP in
origin/destin.

2. Remove (desired value=0) all other NW flights.

3. Remove (desired value=0) all CO flights with DTW, or MEM, or MSP in
origin/destin.

4. Keep (desired value=1) all other CO flights.

5. Keep (desired value=1) all other airline flights.

Formula in cell D3 at this time:
=IF((OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP")*(OR(C3="C
o",c3="nw")))=1,0,if(c3<>"co",if(c3<>"nw",1,"0"),"1"))

This works for all conditions except the row with DTW, IAH, NW - which
gives me gives a value of 0, when the conditions need it to be =1
For reference puposes, the correct values are presented in Column E,
labeled the "desired" column.

Please help me out on this one, thanks

MAX.

 

[Bill Manville]

Please help me out on this one, thanks

=IF(OR(A1="DTW",A1="MEM",A1="MSP",B1="DTW",B1="MEM",B1="MSP"),IF(C1="CO
",0,1),IF(C1="NW",0,1))


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Max]

Try in D3:

=IF(AND(C3="CO",A3<>"DTW",A3<>"MEM",A3<>"MSP",B3<>"DTW",B3<>"MEM",B3<>"MSP")
,1,IF(AND(C3="NW",OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP"))
,1,IF(AND(C3<>"NW",C3<>"CO"),1,0)))

Copy down to D8

 

[Ron Rosenfeld]

I think I can clarify exactly what I desire from my nested if effort:

Table A2:E8
ORIGIN DESTIN AIR NOW DESIRED
DFW IAH 8W 1 1
PHX IAH HP 1 1
DTW IAH NW 0 1
MKE IAH NW 0 0
PIT IAH CO 1 1
IAH DTW CO 0 0

Desired Column Conditions
1. Keep (desired value=1) all NW flights with DTW, or MEM, or MSP in
origin/destin.

2. Remove (desired value=0) all other NW flights.

3. Remove (desired value=0) all CO flights with DTW, or MEM, or MSP in
origin/destin.

4. Keep (desired value=1) all other CO flights.

5. Keep (desired value=1) all other airline flights.

Formula in cell D3 at this time:
=IF((OR(A3="DTW",A3="MEM",A3="MSP",B3="DTW",B3="MEM",B3="MSP")*(OR(C3="C
o",c3="nw")))=1,0,if(c3<>"co",if(c3<>"nw",1,"0"),"1"))

This works for all conditions except the row with DTW, IAH, NW - which
gives me gives a value of 0, when the conditions need it to be =1
For reference puposes, the correct values are presented in Column E,
labeled the "desired" column.

Please help me out on this one, thanks

MAX.

The *array-entered* formula:


=--OR(AND(C2="NW",OR(B2=Arprts,A2=Arprts)),AND(C2="CO",AND(B2<>Arprts,A2<>Arprts)),AND(C2<>"NW",C2<>"CO"))

Arprts is a named range, containing in separate cells:
DTW
MEM
MSP

To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.



--ron

 

[PM]

Bill: Thanks a million !! You solved my conundrum, formula works like a charm!!

 

[Max]

Might be missing something here,
but when I tried Bill M's formula
it gave a "1" for the last row
(IAH-DTW-CO)
when it should be a "0"

So, how did it solve your conundrum?

rgds,
Max

 

[Bill Manville]

when I tried Bill M's formula
it gave a "1" for the last row
(IAH-DTW-CO)
when it should be a "0"

Gives me a 0.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Max]

Bill,

I pasted your formula as-is into E1, copied it down to E8,
- i.e. the last row (IAH-DTW-CO) in the table in A2:C8 (as per OP's post)
and it returned a "1".

The formula showing in E8 was:
=IF(OR(A8="DTW",A8="MEM",A8="MSP",B8="DTW",B8="MEM",B8="MSP"),IF(C8="CO
",0,1),IF(C8="NW",0,1))

rgds
Max

 

[Max]

My apologies, Bill.

I found out what I was missing:
there was a line break just after

.....IF(C1="CO

in your formula:

=IF(OR(A1="DTW",A1="MEM",A1="MSP",B1="DTW",B1="MEM",B1="MSP"),IF(C1="CO
",0,1),IF(C1="NW",0,1))

which I unfortunately missed clearing after pasting as-is into E1
(guess I was misled by Excel's accepting the formula
pasted as-is without any error message)

Clearing the line-break removed
the discrepancy observed for the last row
when E1 was copied down to E8

rgds
Max

 

[Bill Manville]

I pasted your formula as-is into E1, copied it down to E8,
- i.e. the last row (IAH-DTW-CO) in the table in A2:C8 (as per OP's post)
and it returned a "1".

I did the same and it returned a 0, as the formula clearly should, since
the OR gives True because B8="DTW" is True, it then uses IF(C8="CO",0,1)
which gives 0 because C8="CO".

Maybe you didn't transcribe the data or the formula correctly.
Could either of the CO be C0?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Max]

Thanks for the response, Bill.

I've just posted in the thread
what I was missing / doing wrong
(about 8 minutes before your response)

It was a line break I missed clearing
after pasting your formula as -is.

My apologies for the trouble...

rgds,
Max