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
Here are the details:

Rule #1 .......Rule #2.....Final Decision

The 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

Report •

September 20, 2009 at 05:39:28

Try this,


The initial true and false results were in columns H and I with this formula in column J


Report •

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 •

September 20, 2009 at 06:22:50

Here is what I get with the formula:

  H       I         J
TRUE	FALSE	Recommend
FALSE	TRUE	Recommend

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 be
IF($D6<(0.1*$C6), TRUE, FALSE)
Note - no double quotes around true and false
and the same for any other formula that you are evaluating to true or false


Report •

Related Solutions

September 20, 2009 at 06:52:06

The 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 •

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
Thank you!

Report •

Ask Question