Looking for help with IP binary conversion formula

**Jayfaas** · 03-14-2022, 07:58 PM

I have the setup shown below where I can type in the binary number for each octet in the IP address and it will help me visualize what I need to add to get the IP address, however if I could just do somewhat of an IF/ELSE statement stating "If there is a 1 in cell x3, add the number above it, and if 0, dont add" and then let it add the entire octet to get the decimal number for that (I hope that makes sense). In the example below, it would add 128 and 64 and not add the other 6 bits which would equal out to 192 for that octet. What formula could I do to achieve this conversion? Secondly, would there be an easy way to do the reverse method, whereby if I was given 192, I could have it convert it to binary across those numbers and give me the binary number?

**TMS** · 03-14-2022, 08:01 PM

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

I think one option would be to use SUMPRODUCT.

**MarvinP** · 03-14-2022, 08:16 PM

Hi Jayfass,

I think you are looking for the BASE() function in Excel to convert Decimal to Binary. =Base(192,2) gives you what you want for the left side of your equation.

The other function in Excel is Bin2Dec() which converts it back. Read about those and see if that isn't what you are looking for.

**JohnTopley** · 03-15-2022, 05:13 AM

See attached:

in A3

=INT($A$4/A2)

in B3 and copy across to H

=INT(($A$4-SUMPRODUCT(($A$2:A2)*($A$3:A3)))/B$2)

in A5

=SUMPRODUCT((A2:H2)*(A3:H3))

Repeat for Other octet with changed ranges