IPv4 CIDR Subnet Usable Host Count & 8-Bit Binary Octet Conversion Formula

Size VPC subnets and decode 8-bit IPv4 octets directly in your IPAM sheet!

In IPv4 Classless Inter-Domain Routing (CIDR), a /P prefix reserves P network bits and leaves (32 - P) host bits, yielding 2^(32 - P) total addresses. For standard subnets (/8 through /30), subtracting the network address and broadcast address leaves (2^(32 - P)) - 2 usable host IPs—while RFC 3021 point-to-point /31 links provide 2 usable addresses and /32 loopback routes represent 1 host!

To convert any decimal IPv4 octet D (0..255) into a zero-padded 8-bit binary string (replacing DEC2BIN, which caps signed 10-bit values and omits leading zeros unless padded), extract each bit weight 128..1 via MOD(INT(D / 2^b), 2)!

Usable IPv4 Host Count from CIDR Prefix Length (/8 to /32)

Computes (2^(32 - B2)) - 2 for /8../30, 2 for /31 point-to-point links, and 1 for /32 host routes.

=IF(OR(B2 < 8, B2 > 32), "Invalid CIDR Prefix", IF(B2 = 32, 1, IF(B2 = 31, 2, (2 ^ (32 - B2)) - 2)))

8-Bit Binary String from Decimal IPv4 Octet (0..255)

Decomposes decimal octet B3 into its exact 8-character binary bitstring.

=IF(OR(B3 < 0, B3 > 255), "Invalid Octet", MOD(INT(B3 / 128), 2) & MOD(INT(B3 / 64), 2) & MOD(INT(B3 / 32), 2) & MOD(INT(B3 / 16), 2) & MOD(INT(B3 / 8), 2) & MOD(INT(B3 / 4), 2) & MOD(INT(B3 / 2), 2) & MOD(B3, 2))

Further reading

• IETF RFC 4632 — Classless Inter-domain Routing (CIDR): The Internet Address Assignment and Aggregation Plan