Welcome Guest
Log In | Register )
You last visited January 21, 2017, 12:28 pm
All times shown are
Eastern Time (GMT-5:00)

Binary code formula

Topic closed. 12 replies. Last post 6 years ago by frenchie.

Page 1 of 1
PrintE-mailLink
frenchie's avatar - Lottery-041.jpg
Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
Posted: October 14, 2010, 5:30 am - IP Logged

Hi everyone,

       I need help to modified a formula, please.

 I have in A1 a number and in B1 this formula below, which generate 18 digits binary code total and the max number is : 262143, and I want to go up to 575757.

       =CONCATENATE(DEC2BIN(INT(A1/512),9),DEC2BIN(MOD(A1,512),9))

What need to be change in this formula to create a total of 20 digits (Not more and not less ) binary code ?

       And I also need it for a total of 22 digits binary code, for the number 3819816. 

The other formula I have is :

     =DEC2BIN((MOD(A1,4294967296)/16777216),8) & DEC2BIN(MOD(A1,16777216)/65536,8) & DEC2BIN(MOD(A1,65536)/256,8) & DEC2BIN(MOD(A1,256),8)

The maximun number you can have with this formula is : 34359738367.

If someone have a better one, no problem, BUT NO MACRO.

                                                                                           I hope someone can help me, Thank you.

    winsumloosesum's avatar - Lottery-060.jpg
    Pennsylvania
    United States
    Member #2218
    September 1, 2003
    5396 Posts
    Offline
    Posted: October 15, 2010, 12:05 am - IP Logged

    Hi everyone,

           I need help to modified a formula, please.

     I have in A1 a number and in B1 this formula below, which generate 18 digits binary code total and the max number is : 262143, and I want to go up to 575757.

           =CONCATENATE(DEC2BIN(INT(A1/512),9),DEC2BIN(MOD(A1,512),9))

    What need to be change in this formula to create a total of 20 digits (Not more and not less ) binary code ?

           And I also need it for a total of 22 digits binary code, for the number 3819816. 

    The other formula I have is :

         =DEC2BIN((MOD(A1,4294967296)/16777216),8) & DEC2BIN(MOD(A1,16777216)/65536,8) & DEC2BIN(MOD(A1,65536)/256,8) & DEC2BIN(MOD(A1,256),8)

    The maximun number you can have with this formula is : 34359738367.

    If someone have a better one, no problem, BUT NO MACRO.

                                                                                               I hope someone can help me, Thank you.

    Here you go frenchie,

    Hope this helps!!

    http://www.box.net/shared/9jjxz8alba

      frenchie's avatar - Lottery-041.jpg
      Los Angeles
      United States
      Member #75410
      June 2, 2009
      489 Posts
      Offline
      Posted: October 15, 2010, 3:56 am - IP Logged

      Thank you winsumloosesum,

      I didn't want a macro because I like to be able to move my data around as I construct my file.

      And I don't know nothing about macro, so that's why I asked for a formula, I'm better at it.

           On this one I didn't know how to transfer the macro into my file so I did the other way around, and it works good.

                            Thank you winsum.

       

          But still if there is some guru formula guys out there can you please look at it and convert it to my need ? I would really appreciate it.

       

                                                                                                                             Thank you all,    Frenchie.

        frenchie's avatar - Lottery-041.jpg
        Los Angeles
        United States
        Member #75410
        June 2, 2009
        489 Posts
        Offline
        Posted: October 15, 2010, 5:35 am - IP Logged

        Hi winsumloosesum,

              I'm sorry to tell you that but your macro code is wrong !!!

        I verified it with different formulas and some results were different, I think for the 3 or 4 last digits of the combination, it doesn't do it for each combination, some, the result is good and some other is not.

        when you have a chance can you look at it please, Thank you.

        For example combination : 262345 should give you : 1000000000011001001

        with your macro I get :                                                1000000000011000000

                    I hope I'm right otherwise disregard.

                                                                                                     Thank you.                    Frenchie.

          Raven62's avatar - binary
          New Jersey
          United States
          Member #17843
          June 28, 2005
          51170 Posts
          Offline
          Posted: October 15, 2010, 5:19 pm - IP Logged

          Hi everyone,

                 I need help to modified a formula, please.

           I have in A1 a number and in B1 this formula below, which generate 18 digits binary code total and the max number is : 262143, and I want to go up to 575757.

                 =CONCATENATE(DEC2BIN(INT(A1/512),9),DEC2BIN(MOD(A1,512),9))

          What need to be change in this formula to create a total of 20 digits (Not more and not less ) binary code ?

                 And I also need it for a total of 22 digits binary code, for the number 3819816. 

          The other formula I have is :

               =DEC2BIN((MOD(A1,4294967296)/16777216),8) & DEC2BIN(MOD(A1,16777216)/65536,8) & DEC2BIN(MOD(A1,65536)/256,8) & DEC2BIN(MOD(A1,256),8)

          The maximun number you can have with this formula is : 34359738367.

          If someone have a better one, no problem, BUT NO MACRO.

                                                                                                     I hope someone can help me, Thank you.

          =DEC2BIN(MOD(A1,2^20)/2^16,4)&DEC2BIN(MOD(A1,2^16)/2^8,8)&DEC2BIN(MOD(A1,2^8),8)
          Max=1048575 (20 Digits)

          =DEC2BIN(MOD(A1,2^22)/2^16,6)&DEC2BIN(MOD(A1,2^16)/2^8,8)&DEC2BIN(MOD(A1,2^8),8)
          Max=4194303 (22 Digits)

          A mind once stretched by a new idea never returns to its original dimensions!

            frenchie's avatar - Lottery-041.jpg
            Los Angeles
            United States
            Member #75410
            June 2, 2009
            489 Posts
            Offline
            Posted: October 15, 2010, 6:02 pm - IP Logged

            Hi Raven62,

                      Those 2 formulas are great.        Thank you very much.

             I have another question, if you have time ?

              I would like to breack down the binary code, for each digit ( 20 ) to be in 1 cell each ( 20 )?

            I have this formula given to me by JADELottery but for some reason it doesn't work for the last 4 digits !!?

             The formula is :   =VALUE(MID(TEXT($B1,"000000000000000000000"),CELL("col",C1)-CELL("col",$B1),1))

                                                  Hope you can help !

                                                                                                       Thank you,                      Frenchie.

              Raven62's avatar - binary
              New Jersey
              United States
              Member #17843
              June 28, 2005
              51170 Posts
              Offline
              Posted: October 17, 2010, 10:57 am - IP Logged

              Hi Raven62,

                        Those 2 formulas are great.        Thank you very much.

               I have another question, if you have time ?

                I would like to breack down the binary code, for each digit ( 20 ) to be in 1 cell each ( 20 )?

              I have this formula given to me by JADELottery but for some reason it doesn't work for the last 4 digits !!?

               The formula is :   =VALUE(MID(TEXT($B1,"000000000000000000000"),CELL("col",C1)-CELL("col",$B1),1))

                                                    Hope you can help !

                                                                                                         Thank you,                      Frenchie.

              =VALUE(MID(TEXT($B2,"00000000000000000000"),CELL("col",A$1),1))

              After a copy & paste to the other 19 cells:

              In 17th cell change: "00000000000000000000" to "000000000000000000000"

              In 18th cell change: "00000000000000000000" to "0000000000000000000000"

              In 19th cell change: "00000000000000000000" to "00000000000000000000000"

              In 20th cell change: "00000000000000000000" to "000000000000000000000000"

              A mind once stretched by a new idea never returns to its original dimensions!

                frenchie's avatar - Lottery-041.jpg
                Los Angeles
                United States
                Member #75410
                June 2, 2009
                489 Posts
                Offline
                Posted: October 17, 2010, 5:41 pm - IP Logged

                =VALUE(MID(TEXT($B2,"00000000000000000000"),CELL("col",A$1),1))

                After a copy & paste to the other 19 cells:

                In 17th cell change: "00000000000000000000" to "000000000000000000000"

                In 18th cell change: "00000000000000000000" to "0000000000000000000000"

                In 19th cell change: "00000000000000000000" to "00000000000000000000000"

                In 20th cell change: "00000000000000000000" to "000000000000000000000000"

                Hi Raven62,

                     Thank you for you respond, but I can't get it to work, I don't know what it is but it's really annoying to me.

                      I'm using your formulas and they work perfectly, and then I tried what you described with your new formula to break down the binary code, but it won't work for the last 4 digits !!! I tried many different ways with no success, so i'm stuck.

                           What is interresting, is that it works fine with winsumloosesum version macro, but not with your formula.

                  It might be a small problem, but what ?

                My file change a bit, my Binary code data from the Lexico number start in cell : M5 and down, and my first cell for the first number of the binary code start in cell : O5 to AH5, ( 20 digits )

                                                                     Thank you all, hope someone can help fixing it, Thank you.

                  frenchie's avatar - Lottery-041.jpg
                  Los Angeles
                  United States
                  Member #75410
                  June 2, 2009
                  489 Posts
                  Offline
                  Posted: October 17, 2010, 5:58 pm - IP Logged

                  Here you go frenchie,

                  Hope this helps!!

                  http://www.box.net/shared/9jjxz8alba

                  winsumloosesum,

                             I want to aupologize to you.

                     After been checked your macro is fine, it just that it can't handle large number     ( according to a friend ) I which it would my problem would be solved.

                             I'm sorry, didn't mean any harm,             

                                                                                                                                           Frenchie.

                    Raven62's avatar - binary
                    New Jersey
                    United States
                    Member #17843
                    June 28, 2005
                    51170 Posts
                    Offline
                    Posted: October 18, 2010, 4:00 am - IP Logged

                    Hi Raven62,

                         Thank you for you respond, but I can't get it to work, I don't know what it is but it's really annoying to me.

                          I'm using your formulas and they work perfectly, and then I tried what you described with your new formula to break down the binary code, but it won't work for the last 4 digits !!! I tried many different ways with no success, so i'm stuck.

                               What is interresting, is that it works fine with winsumloosesum version macro, but not with your formula.

                      It might be a small problem, but what ?

                    My file change a bit, my Binary code data from the Lexico number start in cell : M5 and down, and my first cell for the first number of the binary code start in cell : O5 to AH5, ( 20 digits )

                                                                         Thank you all, hope someone can help fixing it, Thank you.

                    =TEXT(DEC2BIN(MOD(A2,2^20)/2^16,4),"0000")&TEXT(DEC2BIN(MOD(A2,2^16)/2^8,8),"00000000")&TEXT(DEC2BIN(MOD(A2,2^8),8),"00000000")

                    Max=1048575 (20 Digits)

                    =MID($B2,CELL("COL",A$1),1)

                    A mind once stretched by a new idea never returns to its original dimensions!

                      frenchie's avatar - Lottery-041.jpg
                      Los Angeles
                      United States
                      Member #75410
                      June 2, 2009
                      489 Posts
                      Offline
                      Posted: October 18, 2010, 6:25 am - IP Logged

                      Thank you Raven,

                               it works perfect with your two formulas.

                      My conditional formating is gone it doesn't want to work, I had it set up to blue, when it hit a digit 1, with the : Equal to : 1.

                               So now I think it's gone need a formula ?

                                                                                             I will look into it,    Thank you again...           Frenchie.

                        Raven62's avatar - binary
                        New Jersey
                        United States
                        Member #17843
                        June 28, 2005
                        51170 Posts
                        Offline
                        Posted: October 18, 2010, 12:38 pm - IP Logged

                        Thank you Raven,

                                 it works perfect with your two formulas.

                        My conditional formating is gone it doesn't want to work, I had it set up to blue, when it hit a digit 1, with the : Equal to : 1.

                                 So now I think it's gone need a formula ?

                                                                                               I will look into it,    Thank you again...           Frenchie.

                        =VALUE(MID($B2,CELL("COL",A$1),1))

                        A mind once stretched by a new idea never returns to its original dimensions!

                          frenchie's avatar - Lottery-041.jpg
                          Los Angeles
                          United States
                          Member #75410
                          June 2, 2009
                          489 Posts
                          Offline
                          Posted: October 18, 2010, 3:25 pm - IP Logged

                          Hi Raven,

                                You did it again, everything works fine now, I want to thank you for your work, this really help me.

                           

                                                                                             Thank you,            Frenchie.