|
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. |
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 |
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 <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. |
Formula needs tweaking - HELP!!
|
Formula needs tweaking - HELP!!
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 |
Formula needs tweaking - HELP!!
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 |
Formula needs tweaking - HELP!!
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 |
Formula needs tweaking - HELP!!
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 |
Formula needs tweaking - HELP!!
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 |
Formula needs tweaking - HELP!!
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 |
| All times are GMT +1. The time now is 02:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com