ExcelBanter - View Single Post - Formula needs tweaking

Ron Rosenfeld

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