Problem with IF statement

[AELLC]

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.

 

[electricpete]

> 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]

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<A1,A1<40),1,0)



=====================================
(2B)+(2B)' ?

 

[AELLC]

Pete,

OK - thanks

 

[MintJulep]

If your ones and zeros are boolean true and false then there is no need for an if.

=AND(A1<40, A1>12)

 

[AELLC]

Good idea too Mintjulep, they frequently are true or false "flags"

 

[electricpete]

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 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)' ?

 

[AELLC]

Pete,
Maybe Evaluate Formula would answer that question.

 

[electricpete]

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<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)' ?

 

[IDS]

Pete - I have just discovered that 6>TRUE = FALSE, but 6>(TRUE*1) = TRUE

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/

 

[Denial]

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]

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 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

http://www.ViscoAnalyser.com

 

[jmw]

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".



JMW

http://www.ViscoAnalyser.com

 

[MintJulep]

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]

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]

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]

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]

This works:

=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.

 

[jmw]

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.

JMW

http://www.ViscoAnalyser.com

 

[ierland]

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