Excel If/And Statements

March 3, 2009 at 07:44:41
Specs: Windows XP, 1.0 Gb

I am using nested IF/AND statements. When I open the spreadsheet and before the data is inputed, I get "FALSE" in the output cell. How can I eliminate this from happening?

See More: Excel If/And Statements

Report •


#1
March 3, 2009 at 08:34:36

It might help if we saw the formula, but I'll take a shot

Perhaps you have something like:

A1 = 1

=IF(A1=0,3)

Since you haven't told the function what to do if the Logical Test is false, the value_if_false defaults to FALSE.

This version would not display FALSE, it would display an empty cell:

=IF(A1=0,3,"")


Report •

#2
March 3, 2009 at 08:53:38

Here is the first part of the four nested IF/AND statements:

=if(and(g4="hn", g9="er"),"20.33", etc

When I open the spreadsheet and before data is input, I get "FALSE" in the output cell. How can I eliminate this from happening?


Report •

#3
March 3, 2009 at 09:51:06

Please re-read my response.

Either I have already given you the solution - you don't have a final value_if_false set so it defaults to FALSE - or you need to give us the entire formula so we can see what's going on.


Report •

Related Solutions

#4
March 3, 2009 at 10:41:15

I tried placing "" in different locations in the statement. I got either "#value" or an error message that the statement was incorrect.

Here is the complete statement:

=IF(AND(PAYROLL!$B$4="hm",C9="er"),20.33,IF(AND(PAYROLL!$B$4="hm",C9="ep"),30.50,IF(AND(PAYROLL!$B$4="cl",C9="er"),18.13,IF(AND(PAYROLL!$B$4="cl",C9="ep"),27.20))))

Thank you.


Report •

#5
March 3, 2009 at 11:29:20

At the very end after the 27.2
add a comma and two quotes.

C9="ep"),27.2,""))))

MIKE


Report •

#6
March 3, 2009 at 11:33:10

As I suspected, you do not have anything set for the value_if_false for the final IF statement.

Keep in mind that Excel evaluates the Nested IF's in order. It doesn't look at the entire statment and come up with answer, it steps through it one IF at a time. Each time an IF is FALSE, it responds to whatever is in the value_if_false field. Prove this to yourself by using the Tools...Formula Auditing...Evaluate Formula feature to step through the formula.

Without data in your cells - or with data that does not satisfy any of the AND functions, all of the IF statements are FALSE. Therefore, when the final IF ...IF(AND(PAYROLL!$B$4="cl",C9="ep"),27.20)))) is evaluated and "fails", Excel doesn't find anything in the value_if_false field, so it defaults to FALSE.

The "" goes in the value_if_false field for the final IF:

...27.20,""))))



Report •

#7
March 3, 2009 at 12:07:27

Perhaps this will make things a bit more clear.

The IF statement needs three things:

1st a Condition to Test
2nd What to do if the Condition is TRUE
3rd What to do if the Condition is FALSE
A comma delimits each section.

=IF(
The Condition to Test is:
AND($B$4="hm",C9="er"),

What to do if the Condition is TRUE
20.33,

What to do if the Condition is FALSE
(We are asking another IF for the FALSE return of our 1st IF)
IF(
The Condition to Test is:
AND($B$4="hm",C9="ep"),

What to do if the Condition is TRUE
30.50,

What to do if the Condition is FALSE
(We are asking another IF for the FALSE return of our 2nd IF)
IF(
The Condition to Test is:
AND($B$4="cl",C9="er"),

What to do if the Condition is TRUE
18.13,

What to do if the Condition is FALSE
(We are asking another IF for the FALSE return of our 3rd IF)
IF(
The condition to Test is:
AND($B$4="cl",C9="ep"),

What to do if the Condition is TRUE
27.20,

(This is where you went awry.)
What to do if the Condition is FALSE
""

Close out all of the statements with matching parens.
))))

Your statement lacked the answer for the final FALSE condition.
Putting in the two quote marks creates an empty cell.

MIKE


Report •

#8
March 3, 2009 at 12:26:33

Thank you both for your detailed response.

Report •


Ask Question