IP Address Range Calculator

[bleauhaus]

Im sure this has been posted but after reading over this forum and others i still cannot find a solution.

Given the first two columns of IP addresses i want to generate the third or even fourth Column

StartIP	EndIP	Total IPs	Total /24s
1.32.0.0	1.32.127.255	32,766	128
2.15.0.0	2.15.255.255	65,534	256
31.206.0.0	31.206.255.255	65,534	256
39.112.0.0	39.127.255.255	1,048,574	4096
42.16.0.0	42.47.255.255	2,097,150	8190
42.186.0.0	42.186.255.255	65,534	256
42.195.0.0	42.195.255.255	65,534	256
49.51.0.0	49.51.255.255	65,534	256
59.110.0.0	59.111.255.255	131,070	512
83.210.0.0	83.210.31.255	8,190	32
85.208.0.0	85.209.255.255	131,070	512
100.0.0.0	100.41.255.255	2,752,512	10752
101.202.0.0	101.202.255.255	65,534	256
104.141.0.0	104.141.255.255	65,534	256

<tbody>
</tbody>

 

[mole999]

Off the top of my head
0.0.0.0 if we work right to left 0 = 0 to 255, and then next is also the same (this you know)

so

dim n, dim m

for n = 0 to 255
for m = 0 to 255

next m
next n

should walk up those two values

obvioulsy you need to constrain n and m respectively

generating two million IPs will outstrip the available row space of Excel as demonstrated with 100.0.0.0

or did I miss the point

 

[Momentman]

Hi,

Not all of us are IP wizards So what's the logic for getting the values in column C and D, once we know that, we should be able to get a formula to do the work

A UDF like this might work but it gives a value 2 greater than what you have

Excel 2010
	A	B	C	D
1	StartIP	EndIP	Total IPs	Total /24s
2	1.32.0.0	1.32.127.255	32768	128
3	2.15.0.0	2.15.255.255	65536	256
4	31.206.0.0	31.206.255.255	65536	256
5	39.112.0.0	39.127.255.255	1048576	4096
6	42.16.0.0	42.47.255.255	2097152	8192
7	42.186.0.0	42.186.255.255	65536	256
8	42.195.0.0	42.195.255.255	65536	256
9	49.51.0.0	49.51.255.255	65536	256
10	59.110.0.0	59.111.255.255	131072	512
11	83.210.0.0	83.210.31.255	8192	32
12	85.208.0.0	85.209.255.255	131072	512
13	100.0.0.0	100.41.255.255	2752512	10752
14	101.202.0.0	101.202.255.255	65536	256
15	104.141.0.0	104.141.255.255	65536	256
Sheet1

Cell Formulas
Range	Formula
C2	=TotalIPs(A2,B2)
D2	=C2/256

Function TotalIPs(C1 As Range, C2 As Range)
   Dim diff(), I As Integer
   TotalIPs = 1
   ReDim Preserve diff(0 To UBound(Split(C1, ".")))
   For I = LBound(Split(C1, ".")) To UBound(Split(C1, "."))
        TotalIPs = TotalIPs * (Split(C2, ".")(I) - Split(C1, ".")(I) + 1)
   Next I
   'TotalIPs = TotalIPs - 2
End Function

 

[bleauhaus]

Thanks Guys

As Momentman pointed out let me paint a little better picture... Looking at a single range and quickly determining its size (manually) such as

	A	B	C	D
1	StartIP	EndIP	Total IPs	Total /24s
2	1.32.0.0	1.32.127.255	32768	128

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

only takes a second but you have to compare each of the four octets (the number between the dots) then calculate in your head the sum difference. its not a big deal with a single range but when your dealing with a column of ranges as in the original table A an B - trying to determine the largest or smallest can be an irritating task. with a hundred ranges it quickly becomes near impossible. check out The TCP/IP Guide - IP Classless Addressing Block Sizes and "Classful" Network Equivalents

 

[Peter_SSs]

As Momentman pointed out let me paint a little better picture...

Well, you really didn't do that as Momentman asked for the logic of getting the values in columns C and D. All you told us on that front was "calculate in your head".

check out The TCP/IP Guide - IP Classless Addressing Block Sizes and "Classful" Network Equivalents

Sorry, but it is up to you to understand what is required and explain it to us, not for us to go researching a topic we know nothing about.


Going on what Momentman did with his udf, here's a formula solution that produces the same results. Not the results you quoted, but you haven't told us how to get those numbers. Subtracting 2 would do it but I don't know the logic for why you would do that.

Excel Workbook
	A	B	C
1	StartIP	EndIP	Total IPs
2	1.32.0.0	1.32.127.255	32,768
3	2.15.0.0	2.15.255.255	65,536
4	31.206.0.0	31.206.255.255	65,536
5	39.112.0.0	39.127.255.255	1,048,576
6	42.16.0.0	42.47.255.255	2,097,152
7	42.186.0.0	42.186.255.255	65,536
8	42.195.0.0	42.195.255.255	65,536
9	49.51.0.0	49.51.255.255	65,536
10	59.110.0.0	59.111.255.255	131,072
11	83.210.0.0	83.210.31.255	8,192
12	85.208.0.0	85.209.255.255	131,072
13	100.0.0.0	100.41.255.255	2,752,512
14	101.202.0.0	101.202.255.255	65,536
15	104.141.0.0	104.141.255.255	65,536
Sheet2

 

[Momentman]

1. Did you see the solution i posted above?

2. Should the answer to the first example be 32768 or 32766? and whats your formula for getting the values in column D?

 

[FormR]

Here is another formula attempt:

Excel Workbook
	A	B	C	D
1	StartIP	EndIP	Total IPs	Total /24s
2	1.32.0.0	1.32.127.255	32768	128
3	2.15.0.0	2.15.255.255	65536	256
4	31.206.0.0	31.206.255.255	65536	256
5	39.112.0.0	39.127.255.255	1048576	4096
6	42.16.0.0	42.47.255.255	2097152	8192
7	42.186.0.0	42.186.255.255	65536	256
8	42.195.0.0	42.195.255.255	65536	256
9	49.51.0.0	49.51.255.255	65536	256
10	59.110.0.0	59.111.255.255	131072	512
11	83.210.0.0	83.210.31.255	8192	32
12	85.208.0.0	85.209.255.255	131072	512
13	100.0.0.0	100.41.255.255	2752512	10752
14	101.202.0.0	101.202.255.255	65536	256
15	104.141.0.0	104.141.255.255	65536	256
Sheet2

 

[shg]

Is that correct? Change the last octet in A2 to 255.

 

[FormR]

Is that correct? Change the last octet in A2 to 255.

I don't know to be honest, mine returns 256 - is that not correct?

Excel Workbook
	N
3	2.15.0.255
4	2.15.1.255
5	2.15.etc.255
6	2.15.etc.255
7	2.15.254.255
8	2.15.255.255
Sheet1

 

[shg]

An IP address is an unsigned 32-bit number. The four octets (each 0 to 255) are the four bytes, high to low. So ...

The difference between x.y.z.1 and x.y.z.2 is 256^0 = 1

The difference between x.y.1.z and x.y.2.z is 256^1 = 256

The difference between x.1.y.z and x.1.y.z is 256^2 = 65536

The difference between 1.x.y.z and 2.x.y.z is 256^3 = 16777216