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

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

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.

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.

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!

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.

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!

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.

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.

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!

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.

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!

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.

 Page 1 of 1