Melissa has attended:
Excel Advanced course
Nesting the AND function inside the IF function
Hi - I should be grateful for your assistance to respond to my question
Example:
Cell A8=1
Cell B8=1
Cell C8 =1
I would like to make a rule such that A7=B7=C7 it would say "Match". If it does not match (eg A7=2, B7=1, C7-2), then it would say conflict.
This is the rule that I have attempted to write:
=IF(AND(A8=B8,B8=C8),"Match","Conflict")
Could you pls confirm if this rule is written correctly.
Many thanks
Kindest regards
Melissa
RE: Nesting the AND function inside the IF function
Hi Melissa, thanks for your query. That works fine on my machine!
Hope this helps,
Anthony
RE: Nesting the AND function inside the IF function
Hi Anthony
Thanks for the prompt response.
A follow-up question. How do I expand the formulae/rule such that when at least one of the cells (A8,B8 anc C8) are blank, it would say "blank" instead of Match or Confilct.
This means that I would have two false responses, rather than one in the example above.
Many thanks
Kindest regards
Melissa
RE: Nesting the AND function inside the IF function
Hi Melissa. It will look something like this:
=IF(OR(A8="", B8="", C8=""), "Blank", IF(AND(A8=B8, B8=C8), "Matched", "Conflict"))
Anthony
RE: Nesting the AND function inside the IF function
Hi, I have another example that I hope you could assist with in writing the rule.
Scenario 1 Scenario 2 Scenario 3 Scenario 4
A8 = 1 A9 = 1 A10 =1 A11 = 1
B8 = blank B9 = 1 B10 = blank B11 = 1
C8 = 2 C9 = 1 C10 = 2 C11 = blank
D8 = 3 D9 =3 D10 = 1 D11 = 1
Outcome for scenario 1: Conflict
Outcome for scenario 2: Conflict
Outcome for scenario 3: Match
Outcome for scenario 4: Match
I would like to write a rule such that I can get the outcomes for the four scenarios above. (ie, true = match, false = conflict).
If the input is 1,3 in the same row, there would always be a conflict ignoring the input "2" or blank cells. (ie to achieve a match, we ignore whether an input in a cell is 2 or blank) This explains why scenario 3 is a match because we ignore the C10 input since its a 2 and B10 since it's a blank.
Scenario 4 is a match because all inputs are either 1 or blank. There is no input "3" to cause a conflict.
Scenario 1 and 2 are conflicts because of inputs 1 and 3.
Many thanks in advance.
RE: Nesting the AND function inside the IF function
Hi, i can't seem to find the response to this question.
Let me re-write the scenario examples above because I realised that it did not come out clearly on screen.
Many thanks in advance.
Scenario 1
A8 = 1
B8 = blank
C8 = 2
D8 = 3
Scenario 2
A9 = 1
B9 = 1
C9=1
D9 =3
Scenario 3
A10 =1
B10 = blank
C10 = 2
D10 = 1
Scenario 4
A11 = 1
B11 = 1
C11 = blank
D11 = 1
