Boolean 0/1 instead of True/False

[Guest]

I am using Solver which requires expresions to be linear. In other words
Excel Solver does not allow any IF, COUNTIF or other nonlinear functions. I
want to build a model which includes logical expresions (True/False) results,
and be able to sum the true values. So I want (1/0) instead of (True/False).

So my question is how can I set a cell to be boolean 1/0 ?
Thanks

 

[Harald Staff]

TRUE is 1 and FALSE is 0, try it in any calculation, as in
=4*TRUE-3*FALSE

HTH. Best wishes Harald

 

[Guest]

Thanks Harald,
Your formula works, but it doesn't solve my problem (yet). I have a list of
1000 true or false values in one column and I need to sum all the true
values. And I may not use countif or sumif etc because of linearity problem.
I may use count and sum though.
I tryed your solution adapting it and using sumproduct() and putting all 1's
in the second array. Alas that didnt work.
Any other ideas most welcome.
Thanks
Tony

 

[Guest]

Ok I found a work arround prompted by your solution Harald. I just put a long
line of 1's in a column and multiplied the columns individually into a third
column then I get 0's and 1s in the third column. Long way but it works.
Funny that sumproduct does not produce the same result !!! problem for the
R&D boys and girls.

 

[JE McGimpsey]

Try:

=SUMPRODUCT(--(A1:A1000))

SUMPRODUCT requires a numeric argument, so the double unary minuses
convert TRUE to 1.

 

[Harald Staff]

You have a column of TRUE or FALSE , say in B1:B1000 . TRUE is 1 and FALSE
is 0, so
=SUM(B1:B1000)
will sum all the TRUEs. Or did I misunderstand something ?

HTH. Best wiushes Harald

 

[Harald Staff]

Doh !!!!!!!!! SUM won't work here the way =B1+B2+B3+ ... would (-I never
understood why and I always forget this). Use JE's SUMPRODUCT solution, and
sorry.

Best wu\ishes Harald

 

[Guest]

Thanks JE,

Problem solved. What is the logic behind this syntax, or is it just one of
those things I should know.

Tony
Cape Town - South Africa

 

[Chip Pearson]

The logic is that changes the sign of the element from positive
to negative and back to positive. --X will always equal X.

 

[Dana DeLouis]

...be able to sum the true values.

This seems to work if I understand the question correctly.
=COUNTIF(A1:A10,TRUE)

 

[JE McGimpsey]

See

http://www.mcgimpsey.com/excel/doubleneg.html