Boolean 0/1 instead of True/False
 

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

Boolean 0/1 instead of True/False
 

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

HTH. Best wishes Harald

"Tony" skrev i melding
...
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

Boolean 0/1 instead of True/False
 

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

"Harald Staff" wrote:

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

HTH. Best wishes Harald

"Tony" skrev i melding
...
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

Boolean 0/1 instead of True/False
 

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.

"Tony" wrote:

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

"Harald Staff" wrote:

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

HTH. Best wishes Harald

"Tony" skrev i melding
...
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

Boolean 0/1 instead of True/False
 

Try:

=SUMPRODUCT(--(A1:A1000))

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



In article ,
Tony wrote:

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.

Boolean 0/1 instead of True/False
 

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

"Tony" skrev i melding
...
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

"Harald Staff" wrote:

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

HTH. Best wishes Harald

"Tony" skrev i melding
...
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

Boolean 0/1 instead of True/False
 

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

"Harald Staff" skrev i melding
...
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

Boolean 0/1 instead of True/False
 

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

"JE McGimpsey" wrote:

Try:

=SUMPRODUCT(--(A1:A1000))

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



In article ,
Tony wrote:

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.

Boolean 0/1 instead of True/False
 

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



"Tony" wrote in message
...
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

"JE McGimpsey" wrote:

Try:

=SUMPRODUCT(--(A1:A1000))

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



In article
,
Tony wrote:

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.

Boolean 0/1 instead of True/False
 

...be able to sum the true values.

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

--
HTH. :)
Dana DeLouis

"Tony" wrote in message
...
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

"Harald Staff" wrote:

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

HTH. Best wishes Harald

"Tony" skrev i melding
...
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

Boolean 0/1 instead of True/False
 

See

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

In article ,
Tony wrote:

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