If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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........... |
#2
|
|||
|
|||
Question on LOGIC ?? - PLEASE HELP
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 |
#3
|
|||
|
|||
Question on LOGIC ?? - PLEASE HELP
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........... |
#4
|
|||
|
|||
Question on LOGIC ?? - PLEASE HELP
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........... |
#5
|
|||
|
|||
Question on LOGIC ?? - PLEASE HELP
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........... |
#6
|
|||
|
|||
Question on LOGIC ?? - PLEASE HELP
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........... |
#7
|
|||
|
|||
Question on LOGIC ?? - PLEASE HELP
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 |
#8
|
|||
|
|||
Formula needs tweaking - HELP!!
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. |
#9
|
|||
|
|||
Formula needs tweaking - HELP!!
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 |
#10
|
|||
|
|||
Formula needs tweaking - HELP!!
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. |
|
Thread Tools | |
Display Modes | |
|
|