Excel formulas to calculate IP values such as Netmask, IP range start, IP range end, Broadcast IP, Number of hosts.

excel formulas.md

Excel formulas to calculate IP values - Works in Excel and Google Sheet.

Updated 2023-12-11: Hosts calculation updated to support CIDR or 31 and 32.

Prerequisites:

• A1 contains an IP address, such as 10.0.0.2
• B1 contains the number of bits in the netmask (CIDR) such as 24

The below formulas then go into C1, D1 etc. to perform the various calculations. Some calculations depends on other calculations.

A Google Sheet with all the formulas can be accessed and copied here: https://docs.google.com/spreadsheets/d/1G-vStX0DRB7tq-wvbALnL4HeXYXXOkjh_GriTN-b6-o (Also updated 2023-12-11)

Note, the formulas below are written with the comma spreadsheet notation used in most countries globally. You may need to change the notation to semicolon notation, which is primearly used in the U.S. E.g. =ROUND(PI(),3) and =ROUND(PI();3) are identical but uses different formula notation.

Huge thanks to @6d6163 and @Baribf for pointing out a mistake when used in Excel (The instance_num in Substitute() must not be zero!) and for spotting that the StartIP in a range must be one higher than the range number. They also spotted a copy/paste error mixing up A1 and C1. Their work is what triggred the march 16. 2023 update of these formulas.

C1: Calculate netmask

=BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))
&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))
&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))
&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))

D1: Calculate start of IP range

=BITAND( 
  (LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
  (BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8)))
)
&"."&
BITAND(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
  (BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)))
)
&"."&
BITAND(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)),
  (BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
)
&"."&
BITAND(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), 
  (BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))
)+1

E1: Calculate end of IP range

=BITOR(
  (LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8)))
)
&"."&
BITOR(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)) )
)
&"."&
BITOR(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
)
&"."&
BITOR(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))
)-1

F1: Calculate Broadcast IP

=BITOR(
  (LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8)))
)
&"."&
BITOR(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)))
)
&"."&
BITOR(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
)
&"."&
BITOR(
  (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)),
  (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))
)

G1: Calculate number of hosts

Updated the method of calculating the number of hosts, to peoperly calculate the number of hosts when CIDR is set to 31 (peer-to-peer network) and 32 which is a single IP.

=switch(B1, 31,2, 32,1, (2^(32-B1)-2))

Example

For an IP setup such as 10.100.10.20/20 the following values will be calculated:

• Netmask: 255.255.240.0
• Start IP: 10.100.0.1
• End IP: 10.100.15.254
• Broadcast IP: 10.100.15.255
• Number of hosts: 4094

Thx that really helped me.
However, when I try to calculate the Broadcast address or the IP range I get an #VALUE! error.
Screenshot

Edit: I just noticed that the formulas access C1, what am I suposed to pu into C1?

@RedBadCommander
My apologies for not documenting this correctly. I'll quickly update the documentation, and also add a link to a working Google Sheet which you can copy for working formulas.

I was thrilled to find these formulas as I have a list of IP addresses I need to find the starting and ending IPs. However, like OriCat101 I get the #VALUE! error and testing an IP in the provided Google Sheet seems to show that it still needs some polishing as it doesn't work 100% identifying the proper starting address. Try it with 4.128.0.0/16 and the starting address will be 4.12.0.0 instead of 4.128.0.0 (missing the 8). If you try 4.128.0.0/12 I get 4.0.0.0 as the starting address instead of 4.128.0.0 which is valid.

I don't know if it totally worked for your purpose, but comparing www.calculator.net/ip-subnet-calculator.html it's clear there's a mismatch between the results.

I know that this is not a trivial task to do with just Excel formulas (no vba). Therefore, I commend you for taking the time to make it public and documenting it. You did most of the heavy lifting. If I may you'll find below the updated formulas that returns the same results as the ip-subnet-calculator. I've already spent too much time on this so I won't list the detailed updates but here's 2 least obvious ones: I've replaced the references to C1 by A1 and changed the "instance number" parameter in the SUBSTITUTE function from 0 to 1. I guess the latter is a difference between Google Sheet and MS Excel.

SUBNET MASK
=BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))&"."&
BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))

START IP
=BITAND( (LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)),(BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))))
&"."&
BITAND( (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8)))
&"."&
BITAND( (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)), BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8)))
&"."&
BITAND( (MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8)))+1

END IP
=BITOR((LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))))-1

BROADCAST IP
=BITOR((LEFT(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),1,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),1))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),9,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))+1,FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),2))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),17,8))))
&"."&
BITOR((MID(A1, FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))+1,FIND(CHAR(160),SUBSTITUTE(A1&".",".",CHAR(160),4))-FIND(CHAR(160),SUBSTITUTE(A1,".",CHAR(160),3))-1)), (255-BIN2DEC(MID(REPT("1",B1)&REPT("0",32-B1),25,8))))

Thanks

Thank you for the modified formulas @Baribf, the pointers in your comment saved me a bunch of troubleshooting time.

@f-steff your work here is perfectly suited to some problems I was trying to solve, thank you for showing me the right way to get these calculations done in Excel! As pointed out in the previous comment, there are a few minor glitches to work out in the published .md file, but the heavy lifting of the work is well illustrated. As an Excel junkie myself, I too applaud your work in getting these calculations right!

The '0' argument to 'substitute()' definitely causes errors in Excel, but is silently ignored by Google Sheets; despite the function taking the same input range for the argument, and duplicating functionality exactly. A zero input is undefined in the Google help documentation, as far as I could tell.

The references to C1 instead of A1 appear to just be a typo (see the part of the formulas for calculating the second octet; referencing the netmask here wouldn't make sense in the context and isn't used in the 3rd or 4th octet portions). Otherwise, there are no cross-references/dependencies between the provided formulas in the .md as @f-steff updated back in Sept 2022.

Huge thank you to @6d6163 and @Baribf.
I'm really happy that my work is useful to you, and I will update the entire post to reflect the fixes proposed by both of you as soon as possible.

Again thank you @6d6163 and @Baribf.
The errors, with the exception of the starting address offset, turned out to be editing mistakes that crept in as I prepared the formulas for publication here. Luckily, I don't have to go back and fix things in the sheets I originally made these formulas for :-), but I'm sorry for the extra work this caused you.
While updating the .md file, I also updated the formulas to use all capital letters and changed the line formatting slightly to ensure the lines is as uniform as possible.

This works great. Thank you!

Used a slightly modified version with static 1st & 2nd octet for a subnet planner sheet in Excel and this works amazingly. This added a really cool piece of automation to my network documentation thank you!

When drawing up a list of network segments to use, it is helpful to calculate the next available network number in sequence, based on a starting point network number and its associated subnet mask. The formula below accomplishes this and will automatically roll over into the next higher octet for each part of the IPv4 address when calculating the next available network number.

A1 = IP address in the format "a.b.c.d"
B1 = Subnet mask length

The formula uses quotient and modulus to calculate the correct network number after adding +1 to the broadcast address of the starting point network number provided in A1.

Calculate the next available network number

=MOD(
  BITOR(
    (LEFT(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);1))-1));
    (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);1;8)))
  )
  +
  QUOTIENT(
    BITOR(
      (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);1))+1;FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);1))-1));
      (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);9;8)))
    )
    +
    QUOTIENT(
      BITOR(
        (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))+1;FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))-1));
        (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);17;8)))
      )
      +
      QUOTIENT(
        BITOR(
          (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))+1;FIND(CHAR(160);SUBSTITUTE(A1&".";".";CHAR(160);4))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-1));
          (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);25;8)))
        )
        +1;256
      );256
    );256
  );256
)
&"."&
MOD(
  BITOR(
    (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);1))+1;FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);1))-1));
    (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);9;8)))
  )
  +
  QUOTIENT(
    BITOR(
      (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))+1;FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))-1));
      (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);17;8)))
    )
    +
    QUOTIENT(
      BITOR(
        (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))+1;FIND(CHAR(160);SUBSTITUTE(A1&".";".";CHAR(160);4))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-1));
        (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);25;8)))
      )
      +1;256
    );256
  );256
)
&"."&
MOD(
  BITOR(
    (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))+1;FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);2))-1));
    (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);17;8)))
  )
  +
  QUOTIENT(
    BITOR(
      (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))+1;FIND(CHAR(160);SUBSTITUTE(A1&".";".";CHAR(160);4))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-1));
      (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);25;8)))
    )
    +1;256
  );256
)
&"."&
MOD(
  BITOR(
    (MID(A1; FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))+1;FIND(CHAR(160);SUBSTITUTE(A1&".";".";CHAR(160);4))-FIND(CHAR(160);SUBSTITUTE(A1;".";CHAR(160);3))-1));
    (255-BIN2DEC(MID(REPT("1";B1)&REPT("0";32-B1);25;8)))
  )
  +1;256
)

Thank you so much! Just a few queries:

1. CIDR of 32 doesn't seem to work as expected.
2. Is it possible to improve on this to highlight any duplicate IP or range?

When drawing up a list of network segments to use, it is helpful to calculate the next available network number in sequence, based on a starting point network number and its associated subnet mask. The formula below accomplishes this and will automatically roll over into the next higher octet for each part of the IPv4 address when calculating the next available network number.

Thanks JonasNjopOlsson. This is such a nice addition to the calculations I have made, so after a very brief verification I've added it to the shared spreadsheet without any further optimization.

Thanks burger87.

• CIDR of 32 doesn't seem to work as expected.

Depends on what you expect.
It's impossible to perform any of the calculations with a CIDR of 32, as that only specifies a single IP and not a network range. Likewise, a CIDR of 31 specifies a (rarely used) peer-to-peer network and also not a network range. In both cases, I guess each formula could be expanded to perform a check of the CIDR value and write "invalid" instead of the current wrong IP address. I've chosen not to do so in order not to clutter the formulas more than they already are.
I did update the hosts calculation, as that was very obviously incorrect for CIDR of 31 and 32.
Finally, I added a summary with examples of the above to the shared spreadsheet.

• Is it possible to improve on this to highlight any duplicate IP or range?

For now, these are just formulas to calculate the various IP's in an IP range, but you are free to improve on any area you feel needs improvement, include it in your projects, and share it back with all of us.
I'm not sure what you mean by "highlight any duplicate IP or range?", but if you try to explain further I might give it some thought.

Thank you all for your formulas.

Someone cam make a formula to calculate the Network Address? If you type in 192.168.1.100/24 you get 192.168.1.0
Maybe @JonasNjopOlsson next available network formula is a good starting point, but it's a bit complex for me to solve.

Thank you all for your formulas.

Someone cam make a formula to calculate the Network Address? If you type in 192.168.1.100/24 you get 192.168.1.0 Maybe @JonasNjopOlsson next available network formula is a good starting point, but it's a bit complex for me to solve.

If you use the formula D1: Calculate start of IP range and remove the "+1" at the end, I believe you should get the correct network address based on the provided IP address and associated subnet mask. You do need to put the subnet mask and IP address in separate cells though as the formulas in this gist depend on that.

To rework the formulas to allow putting the subnet mask and IP address in the same cell would require quite a bit of work and add to the complexity of the formulas since extracting the different parts of an address, in any notation, depend on using text extraction and parsing functions in your chosen spreadsheet engine.

@JonasNjopOlsson

If you use the formula D1: Calculate start of IP range and remove the "+1" at the end, I believe you should get the correct network address based on the provided IP address and associated subnet mask. You do need to put the subnet mask and IP address in separate cells though as the formulas in this gist depend on that.

That did the trick, many thanks.

Any chance to add subneting possibilities? So i would enter a network 10.1.1.0/24 wishing to split it in two /25 and get new network addresses (10.1.1.0/25 and 10.1.1.128/25)

@JonasNjopOlsson & @BalurPoco, I added a Network Address column in the spreadsheet. Thank you for pointing it out.

@dorodrigues If I understand you correct, you want to be able to specify an address, the CIDR and a number specifying how many subnets the range should be divided into, and you expect a list as an output.
The basic functionality of what you want is already available in the formulas, but only one calculation is performed per formula.. this makes it easy for you to cascade the calculations in a new spreadsheet.
Specifically with the division you mentioned, if you know you have a /24 network you want to split into two /25, then enter the starting network as a /25 network, and use the resulting Next Available value as the starting point for the next /25 network.

Hi all,
What about using IP as an 32 bit Integer ?
Formula are in french version.

IP to Int (String IP in A1):
=GAUCHE(SUBSTITUE(A1;".";REPT(" ";6));3)*2^24
+STXT(SUBSTITUE(A1;".";REPT(" ";6));8;5)*2^16
+STXT(SUBSTITUE(A1;".";REPT(" ";6));15;7)*2^8
+DROITE(SUBSTITUE(A1;".";REPT(" ";6));3)

Int to IP (Integer IP in A1):
=BITDECALD(A1;24)&"."&BITET(BITDECALD(A1;16);255)&"."&BITET(BITDECALD(A1;8);255)&"."&BITET(A1;255)

Examples:
Once your network adress is converted to interger, just add 1 to its value to get first IP address of the subnet.
You can also divide or get modulo of shift bits to find next subnet according CIDR bits, of find last IP, or to check if 2 IPs are in the same subnet
etc...

Have fun
JP

The sheet with my short integer formulas: https://docs.google.com/spreadsheets/d/1RZIGXa6kjmCpW4rYobd4OpkNMtD382kDGfl5gkvniBY

Thank you @jphir34. You are 100% correct that this is an option - it just wan't a option in the past.
Recently I also made a complete spreadsheet with all the formulas in this gist using 32bit integer math. I just haven't had time to make a new article to describe it as I did here.
I'll enjoy reading your solution and will soon publish my own version.

I like playing with bits, that reminds me old-time and assembly programming :)