Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
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 not display FALSE, it would display an empty cell:
=IF(A1=0,3,"")
0 
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?
0
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.
0
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.
0
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,""))))
0
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
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
