CODEWITHSUNDEEP
excel-formula
ScottyE
ScottyE

Reputation: 1

Error with formula trying to use IF(AND with three conditions

I am trying to write a formula to evaluate all possible values in three cells and score them in another. Excel says that there is a problem with my formula, but I can't locate it. Your help is appreciated.

I am using IF(AND throughout my spreadsheet, but only with two values. Those formulas are accepted by Excel.

=IF(AND(B2="No",B3="Red",B5="No"),0,
 IF(AND(B2="No",B3="Green",B5="No",2,
 IF(AND(B2="No",B3="Blue",B5="No",3,
 IF(AND(B2="No",B3="Yellow",B5="No",5,5,
 IF(AND(B2="No",B3="Red",B5="Yes"),0,
 IF(AND(B2="No",B3="Green",B5="Yes"),2,
 IF(AND(B2="No",B3="Blue",B5="Yes",3,
 IF(AND(B2="No",B3="Yellow",B5="Yes",5,
 IF(AND(B2="Yes",B3="Red",B5="Yes",0,
 IF(AND(B2="Yes",B3="Green",B5="Yes",1,
 IF(AND(B2="Yes",B3="Blue",B5="Yes",2,
 IF(AND(B2="Yes",B3="Yellow",B5="Yes",5,
 IF(AND(B2="Yes",B3="Red",B5="No",0,
 IF(AND(B2="Yes",B3="Green",B5="No",1,
 IF(AND(B2="Yes",B3="Blue",B5="No",2,
 IF(AND(B2="Yes",B3="Yellow",B5="No",3))))))))))))))))

Excel states "There is a problem with this formula".

Upvotes: 0

Views: 58

Answers (3)

xidgel
xidgel

Reputation: 3145

Try this:

=IF(B3="Red",0,
 IF(B3="Green",
   IF(B2="Yes",1,
   IF(B2="No",2,NA())),
 IF(B3="Blue",
   IF(B2="Yes",2,
   IF(B2="No",3,NA())),
 IF(B3="Yellow",
   IF(AND(B2="Yes",B5="No"),3,5),
 NA()))))

Hope that helps

Upvotes: 0

Rick Hitchcock
Rick Hitchcock

Reputation: 35670

You could put your logic in string form in one cell (say, F1):

0NOREDNO 2NOGREENNO 3NOBLUENO 5NOYELLOWNO 0NOREDYES 2NOGREENYES 3NOBLUEYES 5NOYELLOWYES 0YESREDYES 1YESGREENYES 2YESBLUEYES 5YESYELLOWYES 0YESREDNO 1YESGREENNO 2YESBLUENO 3YESYELLOWNO

Then your code reduces to:

=MID(F1, FIND(UPPER(B2&B3&B5),F1)-1, 1)

This would also make it extremely easy to add more conditions in the future.

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

You had a 5,5 where you should have had a 5 and where missing many ) to close the ANDs

=IF(AND(B2="No",B3="Red",B5="No"),0,
 IF(AND(B2="No",B3="Green",B5="No"),2,
 IF(AND(B2="No",B3="Blue",B5="No"),3,
 IF(AND(B2="No",B3="Yellow",B5="No"),5,
 IF(AND(B2="No",B3="Red",B5="Yes"),0,
 IF(AND(B2="No",B3="Green",B5="Yes"),2,
 IF(AND(B2="No",B3="Blue",B5="Yes"),3,
 IF(AND(B2="No",B3="Yellow",B5="Yes"),5,
 IF(AND(B2="Yes",B3="Red",B5="Yes"),0,
 IF(AND(B2="Yes",B3="Green",B5="Yes"),1,
 IF(AND(B2="Yes",B3="Blue",B5="Yes"),2,
 IF(AND(B2="Yes",B3="Yellow",B5="Yes"),5,
 IF(AND(B2="Yes",B3="Red",B5="No"),0,
 IF(AND(B2="Yes",B3="Green",B5="No"),1,
 IF(AND(B2="Yes",B3="Blue",B5="No"),2,
 IF(AND(B2="Yes",B3="Yellow",B5="No"),3,""))))))))))))))))

But I think you can do this with a simpler formula:

=IFERROR(IF(B2 = "No",CHOOSE(MATCH(B3,{"Red","Green","Blue","Yellow"},0),0,2,3,5),IF(B5="Yes",CHOOSE(MATCH(B3,{"Red","Green","Blue","Yellow"},0),0,1,2,5),CHOOSE(MATCH(B3,{"Red","Green","Blue","Yellow"},0),0,1,2,3))),"")

Upvotes: 2

Related Questions