98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Nesting the AND function inside the IF function | Excel forum
Nesting the AND function inside the IF function | Excel forum
Resolved · High Priority · Version 2007
Mon 11 Jun 2012, 10:51Delegate Melissa said...
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
Mon 11 Jun 2012, 11:35Trainer Anthony said...
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
Mon 11 Jun 2012, 11:39Delegate Melissa said...
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
Mon 11 Jun 2012, 11:46Trainer Anthony said...
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
Tue 12 Jun 2012, 22:52Delegate Melissa said...
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.
Wed 13 Jun 2012, 12:43Delegate Melissa said...
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Selecting blank cells within a range of dataSelect the range of data which includes the blank cells that you would like to select. Press the F5 key, this will take you to the GoTo dialogue box where you can click on Special and then select Blanks. |
