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?

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

notdisplay FALSE, it would display an empty cell:=IF(A1=0,3,"")

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?

Please re-read my response. Either I have already given you the solution - you don't have a final

value_if_falseset so it defaults to FALSE - or you need to give us the entire formula so we can see what's going on.

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.

At the very end after the 27.2

add a comma and two quotes.C9="ep"),27.2,""))))

MIKE

As I suspected, you do not have anything set for the value_if_falsefor 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_falsefield. 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

anyof 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 thevalue_if_falsefield, so it defaults to FALSE.The "" goes in the

value_if_falsefield for the final IF:...27.20,""))))

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

Thank you both for your detailed response.

Ask Your Question

Weekly Poll

Do you think broadband providers should eliminate all data caps?

Discuss in The Lounge

Poll History