You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Excel formulas to calculate IP values - Works in Excel and Google Sheet.
============
Updated March 16. 2023
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 march 16. 2023)
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 many 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.
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.
@@ -100,8 +100,9 @@ BITOR(
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.
```excel
=2^(32-B1)-2
=switch(B1, 31,2, 32,1, (2^(32-B1)-2))
```
Example
f-steff
revised
this gist Mar 16, 2023.
1 changed file
with
46 additions
and
52 deletions.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Excel formulas to calculate IP values - Works in Google Sheet, too! :-)
Excel formulas to calculate IP values - Works in Excel and Google Sheet.
============
Updated March 16. 2023
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/edit?usp=sharing
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 march 16. 2023)
Note, the formulas below are written with the comma spreadsheet notation used in many 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.
Note, the formulas below are written with the comma spreadsheet notation used in many 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
* B1 contains the number of bits in the netmask (CIDR) such as 24
Calculate netmask
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/edit?usp=sharing
Note, the formulas below are written with the comma spreadsheet notation used in many 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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
f-steff revised this gist