Convert Values to Binary Code

[CARBOB]

Have no idea what code the values in column L are, but I know they repesent the values in I:K.

Excel Workbook
	H	I	J	K	L
1	06/19/12	3	8	3	0101111111
2	06/18/12	5	5	9	1000101111
3	06/17/12	1	8	3	0010110111
4	06/16/12	0	1	4	0000001110
Sheet1

Excel 2007

 

[rob.barnes01]

Re: Convert Values to Binary Code????

Looks like a DEC2BIN conversion of the concatenated values (383, 559, 183, 014) but with some special formatting applied to force the result to 10 digits. Maybe:

=TEXT(DEC2BIN(I1&J1&K1),"0000000000")

 

[shg]

Re: Convert Values to Binary Code????

M1: =DEC2BIN(SUMPRODUCT(I1:K1,{100,10,1}))

That's limited to values <512, so the second fails.

 

[CARBOB]

Re: Convert Values to Binary Code????

You are correct, the formula works for values less than 512. The database is a txt file using this format
06/19/2012 3 8 3 0101111111
06/18/2012 5 5 9 1000101111
06/17/2012 1 8 3 0010110111
06/16/2012 0 1 4 0000001110
06/15/2012 9 9 2 1111100000

 

[xladept]

Re: Convert Values to Binary Code????

Hi Carbob,

The second one is 512+32+8+4+2+1 or 2^9+2^5+2^3+2^2+2^1+2^0

 

[Rick Rothstein]

Re: Convert Values to Binary Code????

M1: =DEC2BIN(SUMPRODUCT(I1:K1,{100,10,1}))

That's limited to values <512, so the second fails.

Here is a UDF (user defined function) that I have posted in the past which will handle values well past the range of numbers anyone might ever care about...

'  The DecimalIn argument is limited to 79228162514264337593543950266
'  (approximately 96-bits) - large numerical values must be entered
'  as a String value to prevent conversion to scientific notation.
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
  DecToBin = ""
  DecimalIn = CDec(DecimalIn)
  Do While DecimalIn <> 0
    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
  Loop
  If Not IsMissing(NumberOfBits) Then
    If Len(DecToBin) > NumberOfBits Then
      DecToBin = "Error - Number too large for bit size"
    Else
      DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin, NumberOfBits)
    End If
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DecToBin just like it was a built-in Excel function. For your data set as posted in Message #1, this formula would be put in L2 and copied down...

=DecToBin(I2&J2&K2)

 

[CARBOB]

Re: Convert Values to Binary Code????

Here is a UDF (user defined function) that I have posted in the past which will handle values well past the range of numbers anyone might ever care about...

'  The DecimalIn argument is limited to 79228162514264337593543950266
'  (approximately 96-bits) - large numerical values must be entered
'  as a String value to prevent conversion to scientific notation.
Function DecToBin(ByVal DecimalIn As Variant, Optional NumberOfBits As Variant) As String
  DecToBin = ""
  DecimalIn = CDec(DecimalIn)
  Do While DecimalIn<> 0
    DecToBin = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) & DecToBin
    DecimalIn = Int(DecimalIn / 2)
  Loop
  If Not IsMissing(NumberOfBits) Then
    If Len(DecToBin) > NumberOfBits Then
      DecToBin = "Error - Number too large for bit size"
    Else
      DecToBin = Right$(String$(NumberOfBits, "0") & DecToBin, NumberOfBits)
    End If
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DecToBin just like it was a built-in Excel function. For your data set as posted in Message #1, this formula would be put in L2 and copied down...

=DecToBin(I2&J2&K2)

Thank you very much, but I must have did something wrong.

Excel Workbook
	H	I	J	K	L
1	06/19/12	3	8	3	101111111
2	06/18/12	5	5	9	1000101111
3	06/17/12	1	8	3	10110111
4	06/16/12	0	1	4	1110
5	06/15/12	9	9	2	1111100000
6	06/14/12	0	0	0
Sheet1

Excel 2007

Cell Formulas
Range	Formula
L1	=DecToBin(I1&J1&K1)
L2	=DecToBin(I2&J2&K2)
L3	=DecToBin(I3&J3&K3)
L4	=DecToBin(I4&J4&K4)
L5	=DecToBin(I5&J5&K5)
L6	=DecToBin(I6&J6&K6)

 

[Rick Rothstein]

Re: Convert Values to Binary Code????

Thank you very much, but I must have did something wrong.
Sheet1

	H	I	J	K	L
1	06/19/12	3	8	3	101111111
2	06/18/12	5	5	9	1000101111
3	06/17/12	1	8	3	10110111
4	06/16/12	0	1	4	1110
5	06/15/12	9	9	2	1111100000
6	06/14/12	0	0	0

<thead>
</thead><tbody>
</tbody>

Ah, the missing leading zeroes. My fault... I forgot to specify the number of bits to be output via the optional second argument. Try using this formula instead...

=DecToBin(I2&J2&K2,10)

Where the 10 is the number of binary digits to output.

 

[CARBOB]

Re: Convert Values to Binary Code????

Ah, the missing leading zeroes. My fault... I forgot to specify the number of bits to be output via the optional second argument. Try using this formula instead...

=DecToBin(I2&J2&K2,10)

Where the 10 is the number of binary digits to output.

All I can say is Thank you very much, fantastic work!!

Excel Workbook
	H	I	J	K	L
1	06/19/12	3	8	3	1000101111
2	06/18/12	5	5	9	0010110111
3	06/17/12	1	8	3	0000001110
4	06/16/12	0	1	4	1111100000
5	06/15/12	9	9	2	0000000000
6	06/14/12	0	0	0	0010101101
7	06/13/12	1	7	3	1110001001
8	06/12/12	9	0	5	0110011101
9	06/11/12	4	1	3	1000001111
Sheet1

Excel 2007

Cell Formulas
Range	Formula
L1	=DecToBin(I2&J2&K2,10)
L2	=DecToBin(I3&J3&K3,10)
L3	=DecToBin(I4&J4&K4,10)
L4	=DecToBin(I5&J5&K5,10)
L5	=DecToBin(I6&J6&K6,10)
L6	=DecToBin(I7&J7&K7,10)
L7	=DecToBin(I8&J8&K8,10)
L8	=DecToBin(I9&J9&K9,10)
L9	=DecToBin(I10&J10&K10,10)

 

[Rick Rothstein]

Re: Convert Values to Binary Code????

All I can say is Thank you very much, fantastic work!!
Sheet1

	H	I	J	K	L
1	06/19/12	3	8	3	1000101111
2	06/18/12	5	5	9	0010110111
3	06/17/12	1	8	3	0000001110
4	06/16/12	0	1	4	1111100000
5	06/15/12	9	9	2	0000000000
6	06/14/12	0	0	0	0010101101
7	06/13/12	1	7	3	1110001001
8	06/12/12	9	0	5	0110011101
9	06/11/12	4	1	3	1000001111

<thead>
</thead><tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula L1 =DecToBin(I2&J2&K2,10) L2 =DecToBin(I3&J3&K3,10) L3 =DecToBin(I4&J4&K4,10) L4 =DecToBin(I5&J5&K5,10) L5 =DecToBin(I6&J6&K6,10) L6 =DecToBin(I7&J7&K7,10) L7 =DecToBin(I8&J8&K8,10) L8 =DecToBin(I9&J9&K9,10) L9 =DecToBin(I10&J10&K10,10) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Just to point out for clarification... all of your (first) table values are off by one row from the values the function is converting (see the red highlighted text in the first table for but one example). The problem came about because you did not adjust the cell references being passed into the function for the row that the data is on. See the purple highlighted cell in the second table... since the first formula is located in Row 1, the assumption is your data is also in Row 1, so the argument to the function I2&J2&K2 should actually be I1&J1&K1 so that the right cell values are being passed to the function for the row the function is referencing.