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

"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

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

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

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.

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

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

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.

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.

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

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.