Question on LOGIC ?? - PLEASE HELP

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

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

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="ME M",A1="MSP"),OR(C1="DTW",C
1="MEM",C1="MSP")),1,"OTHER"))

Is this what you were after?

Mike



"PM" wrote in message
om...
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...........

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



"Mike" wrote in message
...
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="ME M",A1="MSP"),OR(C1="DTW"
,C
1="MEM",C1="MSP")),1,"OTHER"))

Is this what you were after?

Mike



"PM" wrote in message
om...
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...........

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="ME M",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




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

"Mike" wrote in message ...
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



"Mike" wrote in message
...
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="ME M",A1="MSP"),OR(C1="DTW"
,C
1="MEM",C1="MSP")),1,"OTHER"))

Is this what you were after?

Mike



"PM" wrote in message
om...
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...........

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

--

Vasant

"PM" wrote in message
om...
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="ME M",B5="MSP")*(OR(C5="CO",C
5="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




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

"Mike" wrote in message
...
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



"Mike" wrote in message
...
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="ME M",A1="MSP"),OR(C1="DTW"
,C
1="MEM",C1="MSP")),1,"OTHER"))

Is this what you were after?

Mike



"PM" wrote in message
om...
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...........

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="ME M",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









"Eric" wrote in message ...
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

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="ME M",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.

Pm wrote:
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

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
--
hth
Max
-----------------------------------------
Please reply in thread

Use xdemechanik atyahoodotcom for email
----------------------------------------------------------------------------
"PM" wrote in message
om...
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="ME M",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.