Convert Binary Number to Decimal in Excel

Convert Binary Number to Decimal in Excel
Last Updated on 19/04/2024
Reading time: 3 minutes

There are 2 ways to convert binary numbers to decimal in Excel

  1. 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.

  2. 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.

  1. Write the BIN2DEC function in a cell
  2. The argument is the binary number
BIN2DEC function

But if your binary number has more than 10 bits, the function returns an error.

BIN2DEC manages only 9 digits

It's easy to check this fact with the opposite function; DEC2BIN.

Check value over 511 with binary number

Technique to convert Binary number to Decimal

The easiest way to do the conversion is to

  1. Write each digit of the binary in a cell
  2. Write a geometric sequence (1, 2, 4, 8, 16, ...) but in reverse order
Table to convert binary number to decimal
  1. Next, you multiply every digit by the corresponding value of the geometric sequence directly below it.
  2. And you do the SUM of the new calculated value.
Multiple each value of the geometric sequence by each digit and the sum convert the binary to decimal

Alternatively, with Excel 365, you can simplify the calculation by summing the product of the two initial ranges.

In Excel 365 multiply the 2 ranges to simplify the formula to convert binary to decimal

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.

Create a geographic sequence with MAP

Extract each digit in a cell in reverse order

To begin, let's isolate each digit of the binary number into individual cells.

Extract each digit in a cell

To reverse the order of the digits, we must start the SEQUENCE function from the last digit with a step of -1.

binary digit in reverse order
  • 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.

Custom function to convert binary number to decimal

Or you can create a LAMBDA function.

Leave a Reply

Your email address will not be published. Required fields are marked *

Convert Binary Number to Decimal in Excel

Reading time: 3 minutes
Last Updated on 19/04/2024

There are 2 ways to convert binary numbers to decimal in Excel

  1. 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.

  2. 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.

  1. Write the BIN2DEC function in a cell
  2. The argument is the binary number
BIN2DEC function

But if your binary number has more than 10 bits, the function returns an error.

BIN2DEC manages only 9 digits

It's easy to check this fact with the opposite function; DEC2BIN.

Check value over 511 with binary number

Technique to convert Binary number to Decimal

The easiest way to do the conversion is to

  1. Write each digit of the binary in a cell
  2. Write a geometric sequence (1, 2, 4, 8, 16, ...) but in reverse order
Table to convert binary number to decimal
  1. Next, you multiply every digit by the corresponding value of the geometric sequence directly below it.
  2. And you do the SUM of the new calculated value.
Multiple each value of the geometric sequence by each digit and the sum convert the binary to decimal

Alternatively, with Excel 365, you can simplify the calculation by summing the product of the two initial ranges.

In Excel 365 multiply the 2 ranges to simplify the formula to convert binary to decimal

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.

Create a geographic sequence with MAP

Extract each digit in a cell in reverse order

To begin, let's isolate each digit of the binary number into individual cells.

Extract each digit in a cell

To reverse the order of the digits, we must start the SEQUENCE function from the last digit with a step of -1.

binary digit in reverse order
  • 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.

Custom function to convert binary number to decimal

Or you can create a LAMBDA function.

Leave a Reply

Your email address will not be published. Required fields are marked *