There are 2 ways to convert binary numbers to decimal in Excel
- The BIN2DEC function
This function has only one argument, the binary number, and the function converts it to decimal. But, if your binary number exceeds 10 bits or the value is greater than 512, the function BIN2DEC will return an error.
- Create a custom function
If BIN2DEC returns an error, you must use our custom function to convert your binary number.
The BIN2DEC function
The BIN2DEC function (binary to decimal) is very simple to use.
- Write the BIN2DEC function in a cell
- The argument is the binary number
But if your binary number has more than 10 bits, the function returns an error.
It's easy to check this fact with the opposite function; DEC2BIN.
Technique to convert Binary number to Decimal
The easiest way to do the conversion is to
- Write each digit of the binary in a cell
- Write a geometric sequence (1, 2, 4, 8, 16, ...) but in reverse order
- Next, you multiply every digit by the corresponding value of the geometric sequence directly below it.
- And you do the SUM of the new calculated value.
Alternatively, with Excel 365, you can simplify the calculation by summing the product of the two initial ranges.
But how can the problem be solved without using a conversation table? This is precisely where our LAMBDA function comes into play.
Create an array for the geometric sequence.
Among the Excel 365 array functions, the MAP function will help us build a geometric sequence.
Extract each digit in a cell in reverse order
To begin, let's isolate each digit of the binary number into individual cells.
To reverse the order of the digits, we must start the SEQUENCE function from the last digit with a step of -1.
- We have our 2 arrays
- We have to multiply one by the other to convert our binary number to decimal.
Create the custom function to convert binary to decimal
Now, we must multiply the 2 arrays in a SUM function.
Or you can create a LAMBDA function.
