Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
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 type of airline schedule 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 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 cell on the sheet i.e. Cell F3 = 1 3. Finally, if cell E4 = AA, I would like the 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........... Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
At this point, you might consider a custom VBA function, like this:
Function MyFunction() As Integer Dim strAirline As String Dim strOrigin As String Dim strDestination As String Dim blnCheckThis As Boolean Dim rng As Range Set rng = ActiveCell strAirline = rng.Offset(0, -1).Value 'Value in E strOrigin = rng.Offset(0, -5).Value 'Value in A strDestination = rng.Offset(0, -3).Value 'Value in C If strOrigin = "DTW" _ Or strOrigin = "MEM" _ Or strOrigin = "MSP" _ Or strDestination = "DTW" _ Or strDestination = "MEM" _ Or strDestination = "MSP" Then blnCheckThis = True End If Select Case strAirline Case "CO" If blnCheckThis Then MyFunction = 0 Exit Function End If Case "NW" If blnCheckThis Then MyFunction = 1 Exit Function End If Case "AA" MyFunction = 1 Exit Function End Select 'This is what the function returns 'if it doesn't meet any of your criteria MyFunction = 999 End Function In , Learning VBA typed: 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 type of airline schedule 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 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 cell on the sheet i.e. Cell F3 = 1 3. Finally, if cell E4 = AA, I would like the 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........... Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
If I understand you correctly, you need something like this:
=IF(E2="AA",1,IF(OR(A2="DTW",A2="MEM",A2="MSP",C2= "DTW",C2="MEM",C2="MSP"),I F(E2="NW",1,IF(E2="CO",0,"ERROR")),"ERROR")) Since you haven't said what you want if none of your criteria are met, I have used "ERROR" for that contingency. -- Vasant "Learning VBA" wrote in message ... 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 type of airline schedule 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 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 cell on the sheet i.e. Cell F3 = 1 3. Finally, if cell E4 = AA, I would like the 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........... Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
MY PREVIOUS POST WAS INCOMPLETE.....check this out
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="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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
Vasant, Diana: We are getting close...........
After your formulas, I think I can now clarify exactly what I desire from this effort: Table A2:E8 ORIGIN DFW PHX DTW MKE PIT IAH 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 =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. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
Vasant:
Apologize for the multi-post. I have restated the problem, and may be you could help me out. Your previous response was close, however this is what I desired. So, here it is: 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 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. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
Try this formula:
=OR(OR(A2={"DTW","MEM","MSP"},C2={"DTW","MEM","MSP "}),NOT(E2="NW"))*NOT(AND(OR(A2={"DTW","MEM","MSP" },C2={"DTW","MEM","MSP"}),E2="CO")) -- Learning VBA wrote in message ... MY PREVIOUS POST WAS INCOMPLETE.....check this out 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="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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Logic needs a Doctor !! FORMULA FIX DESIRED!!
There has to be a shorter way, but:
=IF(OR(AND(C2="NW",A2<"MEM'",A2<"DTW",A2<"MSP", B2<"MEM'",B2<"DTW",B2<" MSP"),AND(C2="CO",A2="MEM'",A2="DTW",A2="MSP",B2=" MEM'",B2="DTW",B2="MSP")), 0,1) -- Vasant "Learning VBA" wrote in message ... Vasant: Apologize for the multi-post. I have restated the problem, and may be you could help me out. Your previous response was close, however this is what I desired. So, here it is: 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 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. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return desired Result | Excel Discussion (Misc queries) | |||
Excel Doctor | Links and Linking in Excel | |||
Excel Doctor | Excel Worksheet Functions | |||
Excel Doctor | Excel Worksheet Functions | |||
Excel Doctor | Links and Linking in Excel |