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

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

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
-----------------------------------------
Use xdemechanik <atyahoo<dotcom for email
----------------------------------------------------------------------------
"Bill Manville" wrote in message
...
Max wrote:
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

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
-----------------------------------------
Use xdemechanik <atyahoo<dotcom for email
------------------------------------------------------------
"Max" wrote in message
...
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
-----------------------------------------
Use xdemechanik <atyahoo<dotcom for email
--------------------------------------------------------------------------
--
"Bill Manville" wrote in message
...
Max wrote:
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 wrote:
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

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
-----------------------------------------
Use xdemechanik <atyahoo<dotcom for email
----------------------------------------------------------------------------
"Bill Manville" wrote in message
...
Max wrote:
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