Problem with IF statement
Problem with IF statement
(OP)
If cell A1 = 24
and cell C1 = IF(40>A1>12,1,0) returns 1, which is correct...
why does cell C2 = IF(12<A1<40,1,0) return 0, which is incorrect?
See attached Excel 2010 file.
and cell C1 = IF(40>A1>12,1,0) returns 1, which is correct...
why does cell C2 = IF(12<A1<40,1,0) return 0, which is incorrect?
See attached Excel 2010 file.





RE: Problem with IF statement
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)' ?
RE: Problem with IF statement
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<A1,A1<40),1,0)
=====================================
(2B)+(2B)' ?
RE: Problem with IF statement
OK - thanks
RE: Problem with IF statement
=AND(A1<40, A1>12)
RE: Problem with IF statement
RE: Problem with IF statement
Knowing that the operator "<" accepts one operand on its left and one on its right, then the expression
12<24<40
could be interpretted as either
(12<24)<40 where (12<24)=1 and entire expression is therefore TRUE
OR
12<(24<40) where (24<40)=1 and entire expression is therefore FALSE
It's not exactly clear why it chose the 2nd approach. Just a curiosity.
=====================================
(2B)+(2B)' ?
RE: Problem with IF statement
Maybe Evaluate Formula would answer that question.
RE: Problem with IF statement
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<TRUE returns TRUE
=6<FALSE returns TRUE
Don't ask me why, but that's what it does
So, analysing the original expression
12<24<40
was interpretted as
(12<24)<40 where (12<24)=TRUE and TRUE<40 is FALSE
=====================================
(2B)+(2B)' ?
RE: Problem with IF statement
Also 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 Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Problem with IF statement
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.
RE: Problem with IF statement
Hence I would probably have written this as:
=IF(A1>12,IF(A1<40,1,0)) which gives 1
or:
=IF(A1<40,IF(A1>12,1,0)) which also gives 1
In 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
www.ViscoAnalyser.com
RE: Problem with IF statement
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".
JMW
www.ViscoAnalyser.com
RE: Problem with IF statement
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.
RE: Problem with IF statement
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)' ?
RE: Problem with IF statement
=0=FALSE returns FALSE
=0=FALSE + 0 returns TRUE
= (0=FALSE) + 0 returns 0
=TRUE + 0 returns 1
=====================================
(2B)+(2B)' ?
RE: Problem with IF statement
=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'.
RE: Problem with IF statement
=IF(AND(12<A1,A1<40),1,0)
After knowing *that* works, I wouldn't care much why the former doesn't work.
Regards,
SNORGY.
RE: Problem with IF statement
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.
JMW
www.ViscoAnalyser.com
RE: Problem with IF statement
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