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

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 :)

Thank you @f-steff and @jphir34

Picking up on the two (French) formulas that @jphir34 included above, I converted them to Excel lambda (and English) functions.

For converting IP addresses to an integer:

my test is

=@LAMBDA(ip,LEFT(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 3) * 2^24
+ MID(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 8, 5) * 2^16
+ MID(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 15, 7) * 2^8
+ RIGHT(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 3))("192.168.121.100")

which yields 3232266596

to use this, in name manager

my name is 'ip2int' (but you can call it anything),

it refers to:

=@LAMBDA(ip,LEFT(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 3) * 2^24 + MID(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 8, 5) * 2^16 + 
MID(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 15, 7) * 2^8 + RIGHT(SUBSTITUTE(@ip, ".", REPT(" ", 6)), 3))

and you call it by =ip2int("192.168.121.100"), which yields 3232266596

For converting an integer to IP address:

my test is:

=@LAMBDA(int,QUOTIENT(int, 2^24) & "." &
 QUOTIENT(MOD(int, 2^24), 2^16) & "." &
 QUOTIENT(MOD(int, 2^16), 2^8) & "." &
 MOD(int, 2^8))(3232266596)

which yields "192.168.121.100"

to use this, in name manager

my name is 'int2ip' (but you can call it anything),

it refers to:

=@LAMBDA(int,QUOTIENT(int, 2^24) & "." &  QUOTIENT(MOD(int, 2^24), 2^16) & "." &  QUOTIENT(MOD(int, 2^16), 2^8) & "." &  
MOD(int, 2^8))'

and you call it by =int2ip(3232266596), which yields "192.168.121.100"

Thank you @dewey-b !
I have added custom formulas at the bottom of my sheet: https://docs.google.com/spreadsheets/d/1RZIGXa6kjmCpW4rYobd4OpkNMtD382kDGfl5gkvniBY