Excel formulas to calculate IP values

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

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

Calculate start of IP range

=BITAND( 
  (LEFT(A1, find(char(160),SUBSTITUTE(A1,".",CHAR(160),0))-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(C1,".",CHAR(160),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))))
  ,
  (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))
)

Calculate end of IP range

=BITOR( 
  (LEFT(A1, find(char(160),SUBSTITUTE(A1,".",CHAR(160),0))-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(C1,".",CHAR(160),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))))
  ,
  (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))))
  ,
  (255-bin2dec(mid(REPT("1",B1)&rept("0",32-B1),25,8)))
)-1

Calculate Broadcast IP

=BITOR( 
  (LEFT(A1, find(char(160),SUBSTITUTE(A1,".",CHAR(160),0))-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(C1,".",CHAR(160),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))))
  ,
  (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))))
  ,
  (255-bin2dec(mid(REPT("1",B1)&rept("0",32-B1),25,8)))
)

Calculate number of hosts

=2^(32-B1)-1

Example

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

Netmask: 255.255.240.0 Start IP: 10.100.0.0 End IP: 10.100.15.254 Broadcast IP: 10.100.15.255 Number of hosts: 4095

Aviator-Coding/excel formulas.md

Select an option

No results found