0

I am trying to learn the fundamentals behind the conversion of binary to decimal/Hex/Oct. For now I am focusing on decimal to binary conversion, without the use of the engineering functions dec2bin. I haven't found much online regarding this.

Thus far I have figured out how to convert a cell with a positive decimal number inside of it into 8 boxes (8 bits) making up 0's and 1's.

The way I have done this is using the MOD and INT functions.

First bit with formula

2nd Bit with formula

In the above two images I show how I manually calculate the process of converting from decimal to binary. The question is, this is the long way of doing it and I would like help to make it so that it is simply two boxes; one with a decimal and the other with the binary, so that when I enter the decimal it is instantly calculated into the binary beside it, as demonstrated in picture 3.

decimal to binary short form

If anyone could help me understand how to make my formula work using just one cell instead of 8 separate and different formulaes, it would be appreciated.

Jonny
  • 19
  • 1
  • 7

1 Answers1

2

Use CONCAT as an Array formula:

=CONCAT(INT(MOD(A3/2^(8-ROW($1:$8)),2)))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

CONCAT was introduced with Office 365 Excel.

enter image description here

If one does not have Concat, this can be done with TEXT and SUMPRODUCT:

=TEXT(SUMPRODUCT(INT(MOD(A3/2^(8-ROW($1:$8)),2))*(10^(8-ROW($1:$8)))),"00000000")

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • It's okay, my excel comes with concatenate, a smaller q here, could you explain this part of the formula " 2^(8-ROW($1:$8)),2))) " just to consolidate my understanding, also what difference is there when dealing with signed decimal values in the range of -128 to 128? – Jonny Oct 17 '18 at 20:24
  • Top the first: the Row(1:8) creates and array `{1,2,3,4,5,6,7,8}` the formula then iterates those doing the math `2^7`,`2^6`,...,`2^0`. As to the second I do not know. Not a math guy, just made your formula into an array formula. – Scott Craner Oct 17 '18 at 20:28
  • But according to my test you will need to enlarge it to a 10 digit return. So change the `8`s to `10` in both formula and the `"00000000"` to `"0000000000"` and it will return the same as `dec2bin` – Scott Craner Oct 17 '18 at 20:32
  • cheers for the heads up. I'll make mention of it in my report during testing. cheers again. – Jonny Oct 17 '18 at 20:41