Binary code formula

Reply #12

frenchie — Mon, 18 Oct 2010 19:25:54 GMT

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.

Reply #11

Raven62 — Mon, 18 Oct 2010 16:38:52 GMT

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

Reply #10

frenchie — Mon, 18 Oct 2010 10:25:39 GMT

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.

Reply #9

Raven62 — Mon, 18 Oct 2010 08:00:13 GMT

=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

Reply #8

frenchie — Sun, 17 Oct 2010 21:58:03 GMT

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.

Reply #7

frenchie — Sun, 17 Oct 2010 21:41:11 GMT

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... [ More ]

Reply #6

Raven62 — Sun, 17 Oct 2010 14:57:15 GMT

=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

Reply #5

frenchie — Fri, 15 Oct 2010 22:02:59 GMT

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.

Reply #4

Raven62 — Fri, 15 Oct 2010 21:19:49 GMT

=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

Reply #3

frenchie — Fri, 15 Oct 2010 09:35:29 GMT

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 r... [ More ]

Reply #2

frenchie — Fri, 15 Oct 2010 07:56:17 GMT

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 apprec... [ More ]

Reply #1

winsumloosesum — Fri, 15 Oct 2010 04:05:45 GMT

Here you go frenchie,

Hope this helps!!

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

Binary code formula

frenchie — Thu, 14 Oct 2010 09:30:22 GMT

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 for... [ More ]