Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Remember Me

Are you an
Engineering professional?
Join Eng-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

#### Feedback

"...The forum looks great! You guys have done a fantastic job on arranging things there...Your site is very precise and fun to visit..."

#### Geography

Where in the world do Eng-Tips members come from?

# Problem with IF statement

 Forum Search FAQs Links Jobs Whitepapers MVPs
 AELLC (Structural) 1 Apr 12 13:55
 If cell A1 = 24and cell C1 = IF(40>A1>12,1,0) returns 1, which is correct...why does cell C2 = IF(12
 electricpete (Electrical) 1 Apr 12 14:47
 > and < are binary operators. The output is 1 or 0.I think you may be comparing results of a first  beauty (two number) comparison (result is 1 or 0) to a third number. Use an and operator to combine results if two binary logical comparisons =====================================(2B)+(2B)'  ?
 electricpete (Electrical) 1 Apr 12 14:54
 My first post was a little garbled (part of that attributable to the platform...Android phone, part attributable to the author).By "binary", I was referring to the fact that < has two input arguments (not that output is 1 or 0)."beauty" should have been "binary"Rewrite your formulas as follows:C1 =IF(AND(40>A1,A1>12),1,0)C2 =IF(AND(12
 AELLC (Structural) 1 Apr 12 15:13
 Pete,OK - thanks
 MintJulep (Mechanical) 1 Apr 12 15:29
 If your ones and zeros are boolean true and false then there is no need for an if.=AND(A1<40, A1>12)
 AELLC (Structural) 1 Apr 12 15:52
 Good idea too Mintjulep, they frequently are true or false "flags"
 electricpete (Electrical) 1 Apr 12 18:44
 It is interesting to study how excel interprets the original equation. Knowing that the operator "<" accepts one operand on its left and one on its right, then the expression12<24<40could be interpretted as either(12<24)<40   where (12<24)=1 and entire expression is therefore TRUEOR12<(24<40)   where (24<40)=1 and entire expression is therefore FALSEIt's not exactly clear why it chose the 2nd approach.  Just a curiosity.  =====================================(2B)+(2B)'  ?
 AELLC (Structural) 1 Apr 12 20:52
 Pete,Maybe Evaluate Formula would answer that question.
 electricpete (Electrical) 1 Apr 12 20:59
 Actually, I made an incorrect assumption that < and > operator would view logical true as the number 1 and logical false as the number 0. That was not correct...In fact, it seems that > and < interpret logical TRUE or FALSE as some number higher higher than any numerical value. For example=6>TRUE     returns    FALSE=6>FALSE     returns    FALSE=6
 IDS (Civil/Environmental) 1 Apr 12 23:52
 Pete - I have just discovered that 6>TRUE = FALSE, but 6>(TRUE*1) = TRUEAlso 6*TRUE = 6.Just when TRUE is treated as having a numerical value of 1, and when it isn't, isn't clear to me, but clearly caution is required! Doug JenkinsInteractive Design Serviceshttp://newtonexcelbach.wordpress.com/
 Denial (Structural) 2 Apr 12 6:04
 ElectricPete,Re "binary" vs "beauty".  Within a couple of days of taking delivery of my new (and first) Android phone, I disabled the autocorrect aspect of its spelling checker.  It was driving me crazy with its unwarranted assumptions about what I was trying to enter.  It was a great improvement, the more so because unrecognised words are still underlined to draw my attention to them.
 jmw (Industrial) 2 Apr 12 6:59
 I have been careful of some If statements (not through any suspicion that Excel might not know what its doing but that I might not, which seems to be the case today).Hence I would probably have written this as:=IF(A1>12,IF(A1<40,1,0)) which gives 1or:=IF(A1<40,IF(A1>12,1,0)) which also gives 1In other words, I treat the two conditions separately and then the nested If function can (ha ha: does) only return a true answer.But if I enter 10 or 41 I don't get the expected answer either. That is, it answers "False" and not "0" as I expected (but at least "FALSE" is a true response, which in your case it is not).It appears the IF(AND) statement gives you a 0 or 1 as appropriate and no other approach so far offered.I don't know whether what I get from my approach is consistent with how Excel behaves or not but in your version I am even less clear what is going on.
 jmw (Industrial) 2 Apr 12 7:10
 PS What I failed to note first time around is that your original expressions seem to always give the same answers whatever the contents of A1.Electric Pete seems to have identified what Excel does as the results are consistent with that whatever the contents of A1. But this seems a fairly innocent trap to fall into. But now I have to wonder why my solution is screwed. If A1 is 10 then my first version returns "False" and the second "0" but if A1 is 40 or more My first version returns "0" and the second version returns "False".
 MintJulep (Mechanical) 2 Apr 12 8:19

#### Quote:

but clearly caution is required!

Or just do math with numbers and logic with Boolean values.

Since things like 6>TRUE have no meaning either as math or as logic it's wasted effort pondering how Excel might manage to evaluate it.
 electricpete (Electrical) 2 Apr 12 11:32
 As I said, it's a curiosity.Excel converts numeric 1 or 0 to logical TRUE/FALSE when placed into the first argument of the if command (for example if(1,trueexpression, falseexpression) returns trueexpression), or when placed in or or and command.And excel converts the other direction (from logical TRUE/FALSE to numerical 1/0) when we combine logical TRUE / FALSE with numeric operands using algebraic operators (+, -, *, /).Based on the above, it might be logical to ASSUME that excel does the same conversion when we attempt to combine logical TRUE / FALSE with numerical operands (including 1,0... don't get hung up on an example of 6) using the particular logical operators:  <, > and =.  That's certainly what I ASSUMEd for my initial (incorrect) analysis on 1 Apr 12 18:44.Such assumption would be wrong as was brought out in this thread.   I think that is a potential trap for those that might be tempted to make that assumption.  You're certainly right that never mixing 1/0 with TRUE/FALSE would avoid it.Denial – you're right, that auto-correct struck again.  I don't get mis-spelled words, but instead I get completely wrong words.  I think I'll keep it on for entertainment sake!  =====================================(2B)+(2B)'  ?
 electricpete (Electrical) 2 Apr 12 11:34
 Here are a few examples of mixing 0/0 with true/false.  They do follow some rules, but I wouldn't rely on remembering those.  Again, I agree it's best not to mix.=0=FALSE    returns    FALSE=0=FALSE + 0    returns    TRUE= (0=FALSE) + 0    returns    0=TRUE + 0    returns    1  =====================================(2B)+(2B)'  ?
 kirrer (Automotive) 3 Apr 12 9:05
 jmw, yours is just a case of a missing 'output' for the first if function. Modify it as such:=IF(A1>12,IF(A1<40,1,0),0)Then both 11 and 41 return a 0, instead of <12 values returning FALSE. The first IF statement, if true, returns the second IF statement. But, if it's false then it doesn't know what to return and just says 'FALSE'.
 SNORGY (Mechanical) 13 Apr 12 14:41
 This works:=IF(AND(12
 jmw (Industrial) 15 Apr 12 18:19
 Are we missing some posts?Between 3rd and 13th I'm pretty sure I said I had used the IF function from the function box which leeds you through the necessary steps rather than write it out from knowledge of the syntax etc. I'm sure I also had some replies.
 ierland (Computer) 29 Apr 12 11:21
 If you would ever have had a look at the sorting order for VLOOKUP you would have noticed that this is for ascending ...; -2; -1; 0; 1; 2; ... ; A-Z; FALSE; TRUE [I use ; as list separator]On the other hand Excel does [if needed] a lot of implicit conversion in which TRUE would be converted to the numerical value 1 and FALSE to 0. Multiplying the Booleans with a number would result in the implicit conversion.Knowing and realizing this would explain all discussions above. Why an easy solution if you can make it complicated?Greetings from the Netherlands

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!