nesting and function inside

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

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

resolvedResolved · High Priority · Version 2007

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



 

Excel tip:

Selecting blank cells within a range of data

Select 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.

View all Excel hints and tips


Server loaded in 0.06 secs.