+ Reply to Thread
Results 1 to 11 of 11

Boolean 0/1 instead of True/False

  1. #1
    Tony
    Guest

    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. #2
    Harald Staff
    Guest

    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" <[email protected]> skrev i melding
    news:[email protected]...
    > 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. #3
    Tony
    Guest

    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" <[email protected]> skrev i melding
    > news:[email protected]...
    > > 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. #4
    Tony
    Guest

    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" <[email protected]> skrev i melding
    > > news:[email protected]...
    > > > 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. #5
    JE McGimpsey
    Guest

    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 <[email protected]>,
    Tony <[email protected]> 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. #6
    Harald Staff
    Guest

    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" <[email protected]> skrev i melding
    news:[email protected]...
    > 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" <[email protected]> skrev i melding
    > > news:[email protected]...
    > > > 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. #7
    Harald Staff
    Guest

    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" <[email protected]> skrev i melding
    news:%[email protected]...
    > 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. #8
    Tony
    Guest

    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 <[email protected]>,
    > Tony <[email protected]> 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. #9
    Chip Pearson
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >> <[email protected]>,
    >> Tony <[email protected]> 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. #10
    Dana DeLouis
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> skrev i melding
    >> news:[email protected]...
    >> > 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. #11
    JE McGimpsey
    Guest

    Re: Boolean 0/1 instead of True/False

    See

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

    In article <[email protected]>,
    Tony <[email protected]> wrote:

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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