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

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

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