# Boolean 0/1 instead of True/False

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

2. ## Re: 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" <Tony@discussions.microsoft.com> skrev i melding
news:024AEF14-5EFB-410B-B104-1A3C995DE69A@microsoft.com...
> 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

3. ## Re: 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" <Tony@discussions.microsoft.com> skrev i melding
> news:024AEF14-5EFB-410B-B104-1A3C995DE69A@microsoft.com...
> > 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

>
>
>

4. ## Re: 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" <Tony@discussions.microsoft.com> skrev i melding
> > news:024AEF14-5EFB-410B-B104-1A3C995DE69A@microsoft.com...
> > > 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

> >
> >
> >

5. ## Re: 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 <49E25F3F-2D01-4A03-BA37-16B1EA773A9E@microsoft.com>,
Tony <Tony@discussions.microsoft.com> 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.

6. ## Re: 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" <Tony@discussions.microsoft.com> skrev i melding
news:49E25F3F-2D01-4A03-BA37-16B1EA773A9E@microsoft.com...
> 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" <Tony@discussions.microsoft.com> skrev i melding
> > news:024AEF14-5EFB-410B-B104-1A3C995DE69A@microsoft.com...
> > > 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

> >
> >
> >

7. ## Re: 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" <innocent@enron.invalid> skrev i melding
news:%236Xz5bpgGHA.1260@TK2MSFTNGP05.phx.gbl...
> 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

8. ## Re: 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 <49E25F3F-2D01-4A03-BA37-16B1EA773A9E@microsoft.com>,
> Tony <Tony@discussions.microsoft.com> 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.

>

9. ## Re: 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" <Tony@discussions.microsoft.com> wrote in message
news:865CB2A3-592E-4DFA-A9BD-EDAFD7A68021@microsoft.com...
> 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
>> <49E25F3F-2D01-4A03-BA37-16B1EA773A9E@microsoft.com>,
>> Tony <Tony@discussions.microsoft.com> 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.

>>

10. ## Re: 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" <Tony@discussions.microsoft.com> wrote in message
news:49E25F3F-2D01-4A03-BA37-16B1EA773A9E@microsoft.com...
> 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" <Tony@discussions.microsoft.com> skrev i melding
>> news:024AEF14-5EFB-410B-B104-1A3C995DE69A@microsoft.com...
>> > 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

>>
>>
>>

11. ## Re: Boolean 0/1 instead of True/False

See

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

In article <865CB2A3-592E-4DFA-A9BD-EDAFD7A68021@microsoft.com>,
Tony <Tony@discussions.microsoft.com> wrote:

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1