# Excel nesting AND and If formulars

Microsoft Excel 2007
September 19, 2009 at 22:43:57
Specs: Windows Vista
 I'm trying (and failing) to nest a complex IF and AND formula..Here are the details:H..................I.................JRule #1 .......Rule #2.....Final DecisionTRUE............FALSE TRUE............TRUE FALSE...........TRUEFALSE...........FALSEThe two rules are If and And formulas already, (Rule 1 is =IF(\$D6<(0.1*\$C6), "TRUE", "FALSE") (Rule 2 is =AND(\$E6>500000,\$F6<=2,\$G6=1) and they are working fine.But in the Final decision column I need to have:"Reject" if both rule 1 and 2 are FALSE"Accept" if both rule 1 and 2 are TRUE"Recommend for evaluation" if rule 1 is TRUE and rule 2 is FALSE or if rule 1 is FALSE and rule 2 is TRUE.I've tried a couple of different ways so far, but have been unsuccessful. This is what I've tried so far:=IF((AND(\$H6=TRUE, \$I6=TRUE)), "Accept", IF((AND(\$H6=TRUE, \$I6=FALSE)), "Recommend further evaluation", IF((AND(\$H6=FALSE, \$I6=TRUE)), "Recommend further evaluation", "Reject")))( But every time it comes up with "Reject" even when it shouldnt)=IF((AND(\$H6="True", \$I6="True")), "Accept", IF(AND(\$H6="False", \$I6="False"), "Reject", "Recommend further evaluation"))(And everytime this one comes up with "recommend further evaluation" even when it shouldn't"I'm thinking I'm going wrong with some brackets somewhere or if its because the Cells under the Rule 1 and 2 columns already have formulas in them and its confusing poor excel..Please Help!!

See More: Excel nesting AND and If formulars

#1
September 20, 2009 at 05:39:28
 Hi,Try this,=IF(AND(H1=TRUE,I1=TRUE),"Accept",IF(AND(H1=FALSE,I1=FALSE),"Reject","Recommend"))The initial true and false results were in columns H and I with this formula in column JRegards

Report •

#2
September 20, 2009 at 06:07:16
 Thanks for your reply.I did try this, but it is still giving me "recommend" answer for everyone.I just dont know what im doing wrong haha.

Report •

#3
September 20, 2009 at 06:22:50
 Hi,Here is what I get with the formula:``` H I J TRUE FALSE Recommend TRUE TRUE Accept FALSE TRUE Recommend FALSE FALSE Reject ```As this doesn't work for you it suggests that the output of your initial formulas are not evaluating to real true or false conditions.The formula IF(\$D6<(0.1*\$C6), "TRUE", "FALSE")should beIF(\$D6<(0.1*\$C6), TRUE, FALSE)Note - no double quotes around true and falseand the same for any other formula that you are evaluating to true or falseRegards

Report •

Related Solutions

#4
September 20, 2009 at 06:52:06
 Bec_WikThe key point of Humar's response is that "TRUE" with the quotes places a text value in the cell while TRUE without the quotes places a logical value in the cell.This places text value in the cell:=IF(\$D6<(0.1*\$C6), "TRUE", "FALSE")This checks for a logical value:=IF((AND(\$H6=TRUE, \$I6=TRUE))...A logical TRUE <> a text TRUE, so your IF statements will always evaluate to the value_if_false.

Report •

#5
September 20, 2009 at 15:41:06
 Thank you so much, it now works!It was definitely the formula in the H column. Will know the difference between logical and text values in future. Thank you!

Report •