|
Complicated If, AND, OR - Conflict
This formula is a "Work-in-progress",
meaning I'm dealing with a rather complicated table structure (currently less than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any records showing "vsmc-sca-NonC" (the 5th If) but instead these same records are being assigned "VMD" (the 2nd If). Can you spot my problem? =IF(D385="Cash","Cash", <<1st If IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH CAROLINA")))),"VMD", <<2nd If IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH CAROLINA")),"bkdf-SCA", << 3rd If IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs", <<4th IF IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS NONCARILION"),"vsmc-sca-NonC", <<5th If "bkdf"))))) << Current Default Much Appreciated.. Jim May |
Complicated If, AND, OR - Conflict
Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when testing is
finished. BTW, any construction like OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... | This formula is a "Work-in-progress", | meaning I'm dealing with a rather complicated table structure (currently less | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | =IF(D385="Cash","Cash", <<1st If | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | CAROLINA")))),"VMD", <<2nd If | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | CAROLINA")),"bkdf-SCA", << 3rd If | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | NONCARILION"),"vsmc-sca-NonC", <<5th If | | "bkdf"))))) << Current Default | | Much Appreciated.. | | Jim May |
Complicated If, AND, OR - Conflict
Thanks Niek -- I did as you said. And I see that on a record that should
come back " "vsmc-sca-NonC" (the 5th If) - the 2nd If Is ALSO COMING Back True, so naturally it is "grabbing" the FIRST If producing TRUE. So, my problem is in my 2nd IF I'm sure. But I'm still stuck with IT !! (the 2nd If (isn't corrent, obviously).. What's it's problem? Thanks, Jim May "Niek Otten" wrote: Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when testing is finished. BTW, any construction like OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... | This formula is a "Work-in-progress", | meaning I'm dealing with a rather complicated table structure (currently less | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | =IF(D385="Cash","Cash", <<1st If | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | CAROLINA")))),"VMD", <<2nd If | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | CAROLINA")),"bkdf-SCA", << 3rd If | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | NONCARILION"),"vsmc-sca-NonC", <<5th If | | "bkdf"))))) << Current Default | | Much Appreciated.. | | Jim May |
Complicated If, AND, OR - Conflict
The second IF contains the construction I mentioned, which always returns TRUE
-- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... | Thanks Niek -- I did as you said. And I see that on a record that should | come back " | "vsmc-sca-NonC" (the 5th If) - the 2nd If Is ALSO COMING Back True, so | naturally it is "grabbing" the FIRST If producing TRUE. So, my problem is in | my 2nd IF I'm sure. But I'm still stuck with IT !! (the 2nd If (isn't | corrent, obviously).. What's it's problem? | | Thanks, | | Jim May | | "Niek Otten" wrote: | | Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when testing is | finished. | | BTW, any construction like | | OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "Jim May" wrote in message ... | | This formula is a "Work-in-progress", | | meaning I'm dealing with a rather complicated table structure (currently less | | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | | | =IF(D385="Cash","Cash", <<1st If | | | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | | CAROLINA")))),"VMD", <<2nd If | | | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | CAROLINA")),"bkdf-SCA", << 3rd If | | | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | | NONCARILION"),"vsmc-sca-NonC", <<5th If | | | | "bkdf"))))) << Current Default | | | | Much Appreciated.. | | | | Jim May | | | |
Complicated If, AND, OR - Conflict
I didn't understand your comment "A must be unequal to at least one of them",
and still don't. Can you put it another way, that I can understand or give an example of what I must do for it in this case to return a FALSE (for the 2nd If)? Thanks, "Niek Otten" wrote: The second IF contains the construction I mentioned, which always returns TRUE -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... | Thanks Niek -- I did as you said. And I see that on a record that should | come back " | "vsmc-sca-NonC" (the 5th If) - the 2nd If Is ALSO COMING Back True, so | naturally it is "grabbing" the FIRST If producing TRUE. So, my problem is in | my 2nd IF I'm sure. But I'm still stuck with IT !! (the 2nd If (isn't | corrent, obviously).. What's it's problem? | | Thanks, | | Jim May | | "Niek Otten" wrote: | | Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when testing is | finished. | | BTW, any construction like | | OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | | "Jim May" wrote in message ... | | This formula is a "Work-in-progress", | | meaning I'm dealing with a rather complicated table structure (currently less | | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | | | =IF(D385="Cash","Cash", <<1st If | | | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | | CAROLINA")))),"VMD", <<2nd If | | | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | CAROLINA")),"bkdf-SCA", << 3rd If | | | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | | NONCARILION"),"vsmc-sca-NonC", <<5th If | | | | "bkdf"))))) << Current Default | | | | Much Appreciated.. | | | | Jim May | | | |
Complicated If, AND, OR - Conflict
I meant this part:
"OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH CAROLINA"))) If K385="SCA/FACS NONCARILION" then it is NOT "SCA/NORTH CAROLINA" so the OR returns TRUE If K385 ="SCA/NORTH CAROLINA" then it is NOT ="SCA/FACS NONCARILION" so the OR returns TRUE And if K385 NOT equals any of both, then it returns TRUE as well -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... |I didn't understand your comment "A must be unequal to at least one of them", | and still don't. Can you put it another way, that I can understand or give | an example of what I must do for it in this case to return a FALSE (for the | 2nd If)? | | Thanks, | | | "Niek Otten" wrote: | | The second IF contains the construction I mentioned, which always returns TRUE | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Jim May" wrote in message ... | | Thanks Niek -- I did as you said. And I see that on a record that should | | come back " | | "vsmc-sca-NonC" (the 5th If) - the 2nd If Is ALSO COMING Back True, so | | naturally it is "grabbing" the FIRST If producing TRUE. So, my problem is in | | my 2nd IF I'm sure. But I'm still stuck with IT !! (the 2nd If (isn't | | corrent, obviously).. What's it's problem? | | | | Thanks, | | | | Jim May | | | | "Niek Otten" wrote: | | | | Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when | testing is | | finished. | | | | BTW, any construction like | | | | OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | | | | | "Jim May" wrote in message ... | | | This formula is a "Work-in-progress", | | | meaning I'm dealing with a rather complicated table structure (currently less | | | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | | | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | | | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | | | | | =IF(D385="Cash","Cash", <<1st If | | | | | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | | | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | | | CAROLINA")))),"VMD", <<2nd If | | | | | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | | CAROLINA")),"bkdf-SCA", << 3rd If | | | | | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | | | | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | | | NONCARILION"),"vsmc-sca-NonC", <<5th If | | | | | | "bkdf"))))) << Current Default | | | | | | Much Appreciated.. | | | | | | Jim May | | | | | | | | | |
Complicated If, AND, OR - Conflict
OK, I think I understand (thanks),,, but who do I "break-out" of my
"imprisionment"? My K Column has 18 unique BillingSystems and in the If(2) I'm trying to INCLUDE ALL Except the "SCA/FACS NONCARILION" and the "SCA/NORTH CAROLIN", meaning any of the 16 (18-the 2); Accordingly, in My If(5) I'm trying to pull in ONLY the 1 BillingSystem "SCA/FACS NONCARILION"; But HOW??? Thanks, Jim May "Niek Otten" wrote: I meant this part: "OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH CAROLINA"))) If K385="SCA/FACS NONCARILION" then it is NOT "SCA/NORTH CAROLINA" so the OR returns TRUE If K385 ="SCA/NORTH CAROLINA" then it is NOT ="SCA/FACS NONCARILION" so the OR returns TRUE And if K385 NOT equals any of both, then it returns TRUE as well -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... |I didn't understand your comment "A must be unequal to at least one of them", | and still don't. Can you put it another way, that I can understand or give | an example of what I must do for it in this case to return a FALSE (for the | 2nd If)? | | Thanks, | | | "Niek Otten" wrote: | | The second IF contains the construction I mentioned, which always returns TRUE | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Jim May" wrote in message ... | | Thanks Niek -- I did as you said. And I see that on a record that should | | come back " | | "vsmc-sca-NonC" (the 5th If) - the 2nd If Is ALSO COMING Back True, so | | naturally it is "grabbing" the FIRST If producing TRUE. So, my problem is in | | my 2nd IF I'm sure. But I'm still stuck with IT !! (the 2nd If (isn't | | corrent, obviously).. What's it's problem? | | | | Thanks, | | | | Jim May | | | | "Niek Otten" wrote: | | | | Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when | testing is | | finished. | | | | BTW, any construction like | | | | OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | | | | | "Jim May" wrote in message ... | | | This formula is a "Work-in-progress", | | | meaning I'm dealing with a rather complicated table structure (currently less | | | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | | | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | | | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | | | | | =IF(D385="Cash","Cash", <<1st If | | | | | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | | | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | | | CAROLINA")))),"VMD", <<2nd If | | | | | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | | CAROLINA")),"bkdf-SCA", << 3rd If | | | | | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | | | | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | | | NONCARILION"),"vsmc-sca-NonC", <<5th If | | | | | | "bkdf"))))) << Current Default | | | | | | Much Appreciated.. | | | | | | Jim May | | | | | | | | | |
Complicated If, AND, OR - Conflict
FYI - got it (and it works) !
=IF(D5="Cash","Cash",IF(AND(OR($D5="Discover",D5=" MASTER",D5="Master card",D5="VISA"),NOT((K5="SCA/FACS NONCARILION"))*NOT((K5="SCA/NORTH CAROLINA"))),"VMD",IF(AND(D5="null",C50,OR(K5="SC A/FACS NONCARILION",K5="SCA/NORTH CAROLINA")),"bkdf-SCA",IF(AND(D5="null",C5<0,OR(K5="SCA/FACS NONCARILION",K5="SCA/NORTH CAROLINA")),"bkdf-SCA-Crs",IF(AND(OR(D5="MASTER",D5="VISA"),K5="SCA/FACS NONCARILION"),"vsmc-sca-NonC",IF(AND(D5="null",C5<0,NOT((K5="SCA/FACS NONCARILION"))*NOT((K5="SCA/NORTH CAROLINA"))),"bkdf-Crs","bkdf")))))) "Jim May" wrote: OK, I think I understand (thanks),,, but who do I "break-out" of my "imprisionment"? My K Column has 18 unique BillingSystems and in the If(2) I'm trying to INCLUDE ALL Except the "SCA/FACS NONCARILION" and the "SCA/NORTH CAROLIN", meaning any of the 16 (18-the 2); Accordingly, in My If(5) I'm trying to pull in ONLY the 1 BillingSystem "SCA/FACS NONCARILION"; But HOW??? Thanks, Jim May "Niek Otten" wrote: I meant this part: "OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH CAROLINA"))) If K385="SCA/FACS NONCARILION" then it is NOT "SCA/NORTH CAROLINA" so the OR returns TRUE If K385 ="SCA/NORTH CAROLINA" then it is NOT ="SCA/FACS NONCARILION" so the OR returns TRUE And if K385 NOT equals any of both, then it returns TRUE as well -- Kind regards, Niek Otten Microsoft MVP - Excel "Jim May" wrote in message ... |I didn't understand your comment "A must be unequal to at least one of them", | and still don't. Can you put it another way, that I can understand or give | an example of what I must do for it in this case to return a FALSE (for the | 2nd If)? | | Thanks, | | | "Niek Otten" wrote: | | The second IF contains the construction I mentioned, which always returns TRUE | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Jim May" wrote in message ... | | Thanks Niek -- I did as you said. And I see that on a record that should | | come back " | | "vsmc-sca-NonC" (the 5th If) - the 2nd If Is ALSO COMING Back True, so | | naturally it is "grabbing" the FIRST If producing TRUE. So, my problem is in | | my 2nd IF I'm sure. But I'm still stuck with IT !! (the 2nd If (isn't | | corrent, obviously).. What's it's problem? | | | | Thanks, | | | | Jim May | | | | "Niek Otten" wrote: | | | | Why try to do this all in one formula? Break it up to intermediate results in different cells and integrate later, when | testing is | | finished. | | | | BTW, any construction like | | | | OR(NOT(A=1),NOT(A=2)) will always return TRUE; A must be unequal to at least one of them | | | | -- | | Kind regards, | | | | Niek Otten | | Microsoft MVP - Excel | | | | | | | | "Jim May" wrote in message ... | | | This formula is a "Work-in-progress", | | | meaning I'm dealing with a rather complicated table structure (currently less | | | than 7 If's), but, right now with 5 if's -- the formula IS NOT picking up any | | | records showing "vsmc-sca-NonC" (the 5th If) but instead these same records | | | are being assigned "VMD" (the 2nd If). Can you spot my problem? | | | | | | =IF(D385="Cash","Cash", <<1st If | | | | | | IF(AND(OR($D385="Discover",D385="MASTER",D385="Mas ter | | | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH | | | CAROLINA")))),"VMD", <<2nd If | | | | | | IF(AND(D385="null",C3850,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | | CAROLINA")),"bkdf-SCA", << 3rd If | | | | | | IF(AND(D385="null",C385<0,OR(K385="SCA/FACS NONCARILION",K385="SCA/NORTH | | | CAROLINA")),"bkdf-SCA-Crs", <<4th IF | | | | | | IF(AND(OR(D385="MASTER",D385="VISA"),K385="SCA/FACS | | | NONCARILION"),"vsmc-sca-NonC", <<5th If | | | | | | "bkdf"))))) << Current Default | | | | | | Much Appreciated.. | | | | | | Jim May | | | | | | | | | |
| All times are GMT +1. The time now is 12:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com