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

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






Bill Manville wrote in message ...
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

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

Use xdemechanik <atyahoo<dotcom for email
----------------------------------------------------------------------------
"PM" wrote in message
om...
Bill: Thanks a million !! You solved my conundrum, formula works like a
charm!!

Bill Manville wrote in message
...
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 <atyahoo<dotcom 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.

On 26 Nov 2003 23:39:35 -0800, (PM) wrote:

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