Solved Need nested if and, i think

May 7, 2013 at 12:39:43
Specs: Windows 7
I am trying create a nested "IF AND". I'm trying to self teach and this one has evaded me. I have "Blue" in A1 and "Green" in A2. Want A3 to = "2" if I have both Blue in A1 and Green in A2. If only one color (either) is loaded in thier respected cell then A3 would = 1. Thanks very much.

See More: Need nested if and, i think

Report •


#1
May 7, 2013 at 13:36:39
✔ Best Answer
Try this:

=IF(AND(A1="blue",A2="green"),2,1)

That will solve your problem of
A3 to = "2" if I have both Blue in A1 and Green in A2. If only one color (either) is loaded in thier respected cell then A3 would = 1.

Now how about we take it one more step?

What if both cells are blank?

Now we need a NESTED IF and an OR

Something like this:

=IF(AND(A1="blue",A2="green"),2,IF(OR(A1="blue",A2="green"),1,0))


MIKE

http://www.skeptic.com/


Report •

#2
May 7, 2013 at 13:41:45
Your question might be missing a condition. What do you want A3 to be if neither A1 contains Blue or B1 contains Green?

You've asked about both words being in their respective cells and about only one of the words being in its respective cell, but what if neither cell contains the proper word?

In other words,what if A1 contains Red and B1 contains Chartreuse?

The simpliest case is 2 for both words being where they're supposed to be and 1 for any other situtation:

=IF(AND(A1="Blue",B1="Green"),2,1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 8, 2013 at 12:43:16
To both - thanks very much for your quick respoonse.

Mike - Awesome - works perfectly all the way thru neither color being entered. I was tracking with my attempt but failed to enter the final ' ,1 ' . Still not sure how the #1 at the end of the statement works however, when I thought it ended with 2 which is the value that I wanted if both colors are present.

Again, very much appreciated

Willie


Mike - hit my head a few times and got it !! dah....

thx again and hope you'll help me in the future - like your style.

Willie


Report •

Related Solutions

#4
May 8, 2013 at 12:48:18
DerbyDad03

Thx much for you reply - I really appreciate the support and responses from you all.

Thx again
Willie


Report •

#5
May 8, 2013 at 12:56:41
Still not sure how the #1 at the end of the statement works however,

The structure of an =IF() statement is:

=IF( condition to test, [value_if_true], [value_if_false] )

note that each section is delimited by a comma.

So, in the first IF statement the Condition_to_test was:

AND(A1="blue",A2="green")

The second section is the value to return IF the Condition returns True,
in our case the Value to Return is the number 2

The third section is the value to return IF the Condition returns False,
in our case the Value to Return is the number 1

And as you can see from the Second Formula,
the Value if True and/or the Value if False
can themselves be =IF() statements, hence the "Nested IF"

MIKE

http://www.skeptic.com/


Report •


Ask Question