What range does an ip fall in formula help

[mwood6275]

ok so here is what I am trying to figure out.

Let's say I have an ip of 192.168.1.1 and I need 13 ips for that range. Thus it becomes 192.168.1.1/28.

so what I need is a formula that where I can put the number of ips needed into a cell, the first ip that I plan to use in the next cell and then it checks the range in returns the / value. below is a named range called range2 which is where the result should come from.

Host Chart
/Mask Hosts
1 2,147,483,648
2 1,073,741,824
3 536,870,912
4 268,435,456
5 134,217,728
6 67,108,864
7 33,554,432
8 16,777,216
9 8,388,608
10 4,194,304
11 2,097,152
12 1,048,576
13 524,288
14 262,144
15 131,072
16 65,536
17 32,768
18 16,384
19 8,192
20 4,096
21 2,048
22 1,024
23 512
24 256
25 128
26 64
27 32
28 16
29 8
30 4
31 2
32 2

 

[Eric W]

Since those are all powers of 2, you can create a formula without using your range2:

	A	B	C
1	192.168.1.1	13	192.168.1.1/28

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula C1 =A1&"/"&32-ROUNDUP(LOG(B1,2),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[mwood6275]

exactly what I needed ... thx so much

 

[Eric W]

You're welcome.

 

[mwood6275]

guess I spoke just a little too soon .. hehe

I only want it to return the /value because the ip address will already be there.

 

[Eric W]

Easy enough:

="/"&32-ROUNDUP(LOG(B1,2),0)

and if you just want the value without the /

=32-ROUNDUP(LOG(B1,2),0)