Complicated If, AND, OR - Conflict

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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="Master
card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH
CAROLINA")))),"VMD", <<2nd If

IF(AND(D385="null",C385>0,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
 
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



| 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="Master
| card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH
| CAROLINA")))),"VMD", <<2nd If
|
| IF(AND(D385="null",C385>0,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
 
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
 
The second IF contains the construction I mentioned, which always returns TRUE

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
| >
| >
| >
| > | 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="Master
| > | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH
| > | CAROLINA")))),"VMD", <<2nd If
| > |
| > | IF(AND(D385="null",C385>0,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
| >
| >
| >
 
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,
 
You might be better off with a UDF (macro generated formula) but:
If the 1st condition is true you are done
Look again at the help for AND & OR.
or(xxx<>"sca etc
 
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


|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
| >
| > | 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
| > | >
| > | >
| > | >
| > | > | 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="Master
| > | > | card",D385="VISA"),OR(NOT((K385="SCA/FACS NONCARILION")),NOT((K385="SCA/NORTH
| > | > | CAROLINA")))),"VMD", <<2nd If
| > | > |
| > | > | IF(AND(D385="null",C385>0,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
| > | >
| > | >
| > | >
| >
| >
| >
 
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
 
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",C5>0,OR(K5="SCA/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"))))))
 
Back
Top