There are lots of ways to convert decimal numbers into binary. Excel has a built in function for small numbers, but you'll need a custom formula for larger ones.
Contents:
Formulas
Convert Small Decimal Numbers:
= DEC2BIN(number)number - The decimal number you wish to convert into binary
(Nearly) Unlimited Length Decimal Number:
=LET(
decimal, number,
DecToBinary, LAMBDA(decimal, IF(decimal < 2, decimal, DecToBinary(INT(decimal / 2)) & MOD(decimal, 2))),
DecToBinary(decimal))number - The decimal number you wish to convert into binary
Note: This formula requires the latest version of Excel, 365.
Explanation
How to Convert Large Decimal Numbers to Binary Form
The DEC2BIN function has a limit +/-511 (or 10 binary characters), if the number exceeds this, a #NUM error will be returned.
To get around this we'll need a custom formula that does the conversion math for us:
=LET(
decimal, number,
DecToBinary, LAMBDA(decimal, IF(decimal < 2, decimal, DecToBinary(INT(decimal / 2)) & MOD(decimal, 2))),
DecToBinary(decimal))Now, if we drop this new formula along side the large decimal numbers, pointing the "decimal" variable to the decimal numbers, the formula will convert any size decimal number into binary form:
Division by Two Method
To start with, the method used here to convert into binary is called the division-by-2 method.
Just as decimal numbers can be broken up and divided into 10s, (tens, hundreds, thousands place) decimal numbers can be divided by 2s.
Using this method, you start off with the number you want to convert, "102" in this case, and divide that number by two.
If it divides evenly, you get a perfect division with a remainder of 0. If not, you get a remainder of 1. Since 102 / 2 = 51 , then 0 will be our first binary digit.
The process is then repeated with the divided number. So, 51 / 2 = 25 with a remainder of 1. This 1 becomes the second binary digit.
This process is repeated until no more division is possible, with the quotient reaching 0, and a final 0 or 1 as the remainder.
The first remainder is known as the "least significant" binary digit, and the last remainder is known as the "most significant" binary digit.
By stacking these digits in reverse order, we get a properly converted binary number. "1100110" being the binary form of 102.
Formula Breakdown
In case you're curious, let's go through this formula and understand how the conversion is taking place.
This formula uses the LET function which allows us to store and call variables repeatedly throughout a formula. The variable "decimal", and the calculation "DecToBinary" are used here.
decimal, number,This first line takes a decimal number, either hardcoded or a cell reference, as an input, and stores the number as "decimal".
Next is the LAMBDA function. The first part:
IF(decimal <2, decimal,Is to handle the decimal numbers 0 and 1. If this part is missing, the formula will convert them to "00" and "01", respectively, which may not be desired.
However if the decimal is 2 or greater than the LAMBDA function calls itself recursively.
DecToBinary(INT(decimal / 2)) & MOD(decimal, 2))),This is the core of the formula. For any number 2 or greater, the formula divides the number by 2 using INT(decimal / 2). This operation performs integer division, basically rounding off the result of dividing the decimal by two, and moves us to the next binary digit position.
The remainder is obtained using MOD(decimal, 2), which is either 0 or 1. This remainder is the actual binary digit corresponding to the current position.
The formula then concatenates the result of the recursive call with "&" with the current binary digit (the remainder). This builds the binary number from right to left, starting with the least significant bit.
The recursion continues until the division results in a number less than 2, at which point the base case of the recursion returns the final digit, and the concatenation process builds the binary number in reverse order.