+ Reply to Thread
Results 1 to 3 of 3

Require help with to create formula 'to add to IPV4 address'

  1. #1
    Registered User
    Join Date
    01-19-2021
    Location
    Toronto, Canada
    MS-Off Ver
    Office 2019
    Posts
    2

    Require help with to create formula 'to add to IPV4 address'

    MS Office 365 ProPlus

    Looking for help to create formula in Excel for below...

    A /23 subnet being broken down. 255.255.254.0 (/23) User input data in cell C12 - ex. 10.203.4.0


    Require formula in cell C13 to return value 10.203.4.1
    255.255.254.0 (/23) Cell C13 - to data in C12 to fourth octet add 1.


    Require formula in cell C14 to return value 10.203.220.16
    255.255.255.240 (/28) Cell C14 - to data in C12 to third octet add 216 & to fourth octet add 16


    Require formula in cell C15 to return value 10.203.220.17
    255.255.255.240 (/28) Cell C15 - to data in C14 add 1 to fourth octet.
    or
    255.255.255.240 (/28) Cell C15 - to data in C12 to third octet add 216 & to fourth octet add 17


    Require formula in cell C16 to return value 10.203.229.88
    255.255.255.248 (/29) Cell C16 - to data in C12 to third octet add 225 & to fourth octet add 88.


    Require formula in cell C17 to return value 10.203.229.92
    255.255.255.248 (/29) Cell C17 - to data in C16 to fourth octet add 4.
    or
    255.255.255.248 (/29) Cell C17 - to data in C12 to third octet add 225 & to fourth octet add 92.


    Require formula in cell C18 to return value 10.203.237.0
    255.255.255.192 (/26) Cell C18 - to data in C12 to third octet add 233.


    Require formula in cell C19 to return value 10.203.237.1
    255.255.255.192 (/26) Cell C19 - to data in C18 to fourth octet add 1.
    or
    255.255.255.192 (/26) Cell C19 - to data in C12 to third octet add 233 & to fourth octet add 1.


    Require formula in cell C20 to return value 10.203.6.0
    255.255.255.248 (/29) Cell C20 - to data in C12 to third octet add 2.


    Require formula in cell C21 to return value 10.203.6.1
    255.255.255.248 (/29) Cell C21 - to data in C20 to fourth octet add 1.
    or
    255.255.255.248 (/29) Cell C21 - to data in C12 to third octet add 2 & to fourth octet add 1.


    Formula to add to any octet any value with condition to check & verify no octet value is above 255 will be even more better.
    Thank you

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Require help with to create formula 'to add to IPV4 address'

    I'm not a network admin, so I don't know the logic for working with subnet masks, but a web search provides MANY hits leading to cheat sheets like this. I figure there may be a way to use that to determine what needs to be added to the C13 value.

    Assuming C12 is entered as text. Define the name s referring to ={1;2;3;4}. Then

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    would be be an array of the 4 octets. You could then add k to the 4th item in this array by adding {0;0;0;k}. If you're really using Excel 2019, you have the TEXTJOIN function, so you could reassemble the resulting array into an IP4 address using TEXTJOIN.

    C13:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The alternative approach is brute force, but it has the advantages of not needing defined names and working in most older Excel versions.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    where 4 is 3+1.

    If you could provide clearer logic for what should be added to which octet, the formulas above could be generalized. If not, every IP4 address would need to be adjusted ad hoc.

  3. #3
    Registered User
    Join Date
    01-19-2021
    Location
    Toronto, Canada
    MS-Off Ver
    Office 2019
    Posts
    2

    Re: Require help with to create formula 'to add to IPV4 address'

    Hello hrlngrv:

    Thank you. Below is some progression I had made prior to submitting my request for help. I have shown the masks ex. '255.255.254.0 (/23)' for reference, no requirement to use in formula.

    I will try your solution.


    MS Office 365 ProPlus

    Cell C12 user input data 10.103.4.0. 255.255.254.0 (/23)

    A colleague created longtime ago following
    =REPLACE(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1)+1,LEN(C12),MIN(255,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1)+1,LEN(C12))+1))

    Applying formula in cell C13 result is 10.103.4.1
    (Cell C13 - to data in C12 to fourth octet add 1). I.e. 10.103.4.0+1. 255.255.254.0 (/23)

    I changed the last digit in formula from 1 to 16
    =REPLACE(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1)+1,LEN(C12),MIN(125,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1)+1,LEN(C12))+16))
    Applying formula in cell C14 result is 10.103.4.16
    However C14 result need to be 10.103.220.16
    (to data in C12 to third octet add 216 & to fourth octet add 16). I.e. 10.103.4+216.0+16. 255.255.254.0 (/23)


    Manipulating formula further got 10.103.220
    =REPLACE(C12,FIND(".",C12,FIND(".",C12)+1)+1,LEN(C12),MIN(255,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1),LEN(C12))+220))


    Manipulating formula more for cell C14, I managed to get a result 10.103.22010.103.16 (not the required end result). Require 10.103.220.16 255.255.255.240 (/28)
    =REPLACE(C12,FIND(".",C12,FIND(".",C12)+1)+1,LEN(C12),MIN(255,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1),LEN(C12))+220))&REPLACE(C12,FIND(".",C12,FIND(".",C12)+1)+1,LEN(C12),MIN(255,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1),LEN(C12))+16))

    Changing a bit more I managed to, cell C14 got 10.103.22016 (not the required end result). Require 10.103.220.16 255.255.255.240 (/28)
    =REPLACE(C12,FIND(".",C12,FIND(".",C12)+1)+1,LEN(C12),MIN(255,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1),LEN(C12))+220))&REPLACE(,FIND(".",C12,FIND(".",C12)),LEN(C12),MIN(255,MID(C12,FIND(".",C12,FIND(".",C12,FIND(".",C12)+1)+1),LEN(C12))+16))


    Calculating from data in cell C12 for cell C15 result should be 10.203.220.17. I.e. 10.103.4+216.0+17. 255.255.255.240 (/28)
    If calculating from data in C14 for cell C15 result should be 10.203.220.17. I.e. 10.103.220.0+1. 255.255.255.240 (/28)


    Calculating from data in cell C12 for cell C16 result should be 10.203.229.88. I.e. 10.103.4+224.0+88. 255.255.255.248 (/29)


    Calculating from data in cell C12 for cell C17 result should be 10.203.229.92. I.e. 10.103.4+224.0+92. 255.255.255.248 (/29)
    If calculating from data in C16 for cell C17 result should be 10.203.229.92. I.e. 10.103.220.0+4. 255.255.255.248 (/29))


    Calculating from data in cell C12 for cell C18 result should be 10.203.237.0. I.e. 10.103.4+233.0+0. 255.255.255.192 (/26)


    Calculating from data in cell C12 for cell C19 result should be 10.203.237.1. I.e. 10.103.4+233.0+1. 255.255.255.192 (/26)
    If calculating from data in C18 for cell C19 result should be 10.203.237.1. I.e. 10.103.237.0+1. 255.255.255.192 (/26)


    Calculating from data in cell C12 for cell C20 result should be 10.203.6.0. I.e. 10.103.4+2.0+0. 255.255.255.248 (/29)


    Calculating from data in cell C12 for cell C21 result should be 10.203.6.1. I.e. 10.103.4+2.0+1. 255.255.255.248 (/29)
    If calculating from data in C20 for cell C21 result should be 10.203.6.1. I.e. 10.103.6.0+1. 255.255.255.248 (/29)

+ 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. Converting an IPv4 range to CIDR format
    By spl0uf in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-04-2023, 10:31 AM
  2. Converting an IPv4 range to CIDR format
    By spl0uf in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-15-2018, 03:03 PM
  3. [SOLVED] Using Address Function to create an address referencing the entire column
    By OliverS in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-28-2015, 01:06 AM
  4. trying to create nested formula using address match vlookup
    By Johnslosch in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-29-2014, 06:37 PM
  5. Hello...IPv4/IPv6 Address and Network Data
    By peterso8 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-21-2013, 12:13 PM
  6. [SOLVED] Require Help - Require Formula for Multiple values in both columns
    By krodge in forum Excel General
    Replies: 6
    Last Post: 01-13-2012, 03:42 AM
  7. [SOLVED] trying to create a formula and require help.
    By Amste in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2006, 04:35 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