Solved: Different expressions of a simple formula

[Aussiebear]

I've recently come across a situation where it was asked for a formula for the following scenario:

A1 is a boolean value (True or False)

A2 formula required to show if A1 value is true then show 1 else 0.

A number of alternatives were provided,

=IF(A1=True,"1","0") which stores the value as Text
=IF(A1=True,1,0) which stores the value as a Number

There were three other alternatives which I hadn't realised could also be used

=IF(A1,1,0) which stores the value as a Number. I'm told this can be used where the A1 is a boolean function.

--A1 which stores the value as a Number, and
N(A1) also stores the value as a Number.

Can someone explain why the last two work?

[mikerickson]

On an worksheet, Excel converts logical values to numbers with FALSE=0 and TRUE=1.
(VB is different, CLng(True) = -1 : CLng(False) = 0, note the negative True.)

Forcing a logical value to do numeric arithmatic is one way to convert logicals to numbers:

--(TRUE) = (-1)*(-1)*(TRUE) = (-1)*(-1)*(1) = 1
--(FALSE) = (-1)*(-1)*(FALSE) = (-1)*(-1)*(0) = 0

another, slightly slower, conversion is =A1+0

The spreadsheet function N converts other data types, including logicals, into numbers.

The arithmetic equivalent of logical operators are:
N(AND(A1,B1)) = N(A1)*N(B1)
N(NOT(A1)) = 1-N(A1)
N(OR(A1,B1)) = SIGN(N(A1) + N(B1))

these are useful when constructing SUMPRODUCT's and other array formulas.

[mdmackillop]

True on a spreadsheet is equal = 1 (enter =True*1 in a cell), False = 0.
--A1 will force True/False into a number as will N(A1)

Actually, it appears any number except 0, including negative equates to True in the IF function Try =IF(4-2,"True","False"), and any other values.

It looks like IF is really saying "If Not Zero, then True, else False"

[mikerickson]

Good catch.

=OR(A1:A10) will test if there are non-zero numbers in that range
=AND(A1:A10) tests if there are any 0's in a range.

Nice!
Thanks, mdmackillop.

[Bob Phillips]

Ted,

It is all to do with coercing a value into a number.

The things that you are alluding to here is why you rarely if ever need IF tests in conditional formatting. SO often I have seen

=IF(condition,TRUE,FALSE)

beacuse the developer knew that CF needs to test TRUE or FALSE, but not realising that the condition returns TRUE or FALSE, so all that is needed is

=condition

shorter, sharper, and more efficient, doubly important in CF.

The double unary is a common trick, widely used in SUMPRODUCT solutions, and I have offered many solutions where I have used a formula as simple as

=--A1

I also use it to setup non-ambiguous dates in formulae, Yanks take note!, such as

=SUMPRODUCT(--(A1:A10>=--"2008-01-01"),--(A1:A10<--"2008-02-01"))

N wouldn't work here, but even where it does, -- is often preferable because it isn't a function.

[Bob Phillips]

Actually, it appears any number except 0, including negative equates to True in the IF function Try =IF(4-2,"True","False"), and any other values.

That is also the case in VBA

?cbool(2)
True
?cbool(-2)
True
?cbool(0)
False

[mdmackillop]

Thanks Bob.

[Aussiebear]

Good on you..... I am struggling to come to terms with Post #5 when you drop #6 on me.

#6 could well pass for Russian co-ordinates to bomb the local chookpen.