Boolean 0/1 instead of True/False

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
TRUE is 1 and FALSE is 0, try it in any calculation, as in
=4*TRUE-3*FALSE

HTH. Best wishes Harald
 
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
 
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.
 
Try:

=SUMPRODUCT(--(A1:A1000))

SUMPRODUCT requires a numeric argument, so the double unary minuses
convert TRUE to 1.
 
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
 
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
 
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
 
The logic is that changes the sign of the element from positive
to negative and back to positive. --X will always equal X.
 
...be able to sum the true values.

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