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