+ Reply to Thread
Results 1 to 4 of 4

Looking for help with IP binary conversion formula

  1. #1
    Registered User
    Join Date
    03-03-2013
    Location
    Charleston, SC
    MS-Off Ver
    Office 2007 Ultimate
    Posts
    18

    Looking for help with IP binary conversion formula

    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?
    Attached Images Attached Images

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,018

    Re: Looking for help with IP binary conversion formula

    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.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,225

    Re: Looking for help with IP binary conversion formula

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: Looking for help with IP binary conversion formula

    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
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Decimal to 16 bits binary conversion in Excel?
    By Dadadadeo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-31-2020, 12:46 PM
  2. [SOLVED] 64 bit binary conversion
    By alonglick in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 08-12-2014, 08:01 AM
  3. [SOLVED] Signed Binary Decimal (B16) Conversion
    By Talamon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2014, 03:34 PM
  4. [SOLVED] Binary (concatenated from Decimal) to Hex Conversion Error
    By Talamon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-19-2014, 12:19 PM
  5. binary conversion
    By binaryuser in forum Excel General
    Replies: 1
    Last Post: 09-08-2006, 09:58 AM
  6. [SOLVED] Hexadecimal to Binary Conversion
    By sean_f in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-04-2006, 04:05 PM
  7. [SOLVED] decimal to binary conversion
    By tam in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2005, 01:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1