How do I Write this 'IF' or 'Lookup' Formula in a Cell with a Resulting Value in a Different Cell?

[coastalcraig]

Sorry I’m such a newbie at this.

• I have a cell - C2, which can have any whole number value between 1 and 223 - except 127. (You may correctly recognize this as the Classful IP address ranges A, B and C).
• I desire to have another cell which refers to C2. This cell is B6. I would like B6 to end up with only 1 of 3 possible values - 8, 16 or 24. What I am looking for isa formula:

1. If cell C2 has a whole number value of 1 through 126, B6 should have a value of 8
2. If cell C2 has a whole number value of 128 through 191, B6 should have a value of 16
3. If cell C2 has a whole number value of 192 through 223, B6 should have a value of 24

I am a complete rookie but did manage an ‘IF’ statement in some other cells. I have not been able to learn how to create an ‘IF’ statement that accomplishes my goal.

I tried every combination of:

=IF(A1<127, A1>0,8,IF(A1<192, A1>127,16)) but this doesn’t seem to work.

I tried to see if I could do it with a range:

=IF(A1=(1:126,8,IF(A1=(128:191,16)) but this didn’t seem to work either.

I have zero experience in formulas but have made a fairly strong effort at this and don’t know what to do


You folks are extremely generous to offer assistance. I would really appreciate it if you helped me out with this.

Thanks,

coastalcraig

 

[pplstuff]

=if(c2<=126,8,if(c2<=191,16,if(c2<=223,24)))


You could add:
=if(c2<=126,8,if(c2<=191,16,if(c2<=223,24,"Error")))

 

[njimack]

Welcome to the board. You COULD do this with an IF formula, but the below function is preferable:
=LOOKUP(C2,{0,126,191,223},{8,16,24})

 

[Jonmo1]

Welcome to the board.

Try

=IF(C2<127,8,IF(C2<192,16,24))


This is assuming the Cell C2 will NOT contain 127 or a number higher than 223
The formula doesn't actually verify that.

 

[bstory84]

As you can see there are many ways to do it.
njimack version looks really good.
Except you need to move the values up 1 digit.
=LOOKUP(C2,{0,127,192,224},{8,16,24})
here is the long not so good way but still gets the job done.

=IF(AND(C2>0,C2<=126),8,IF(AND(C2>=128,C2<=191),16,IF(AND(C2>=192,C2<=223),24,"NA")))

 

[Jeff Meyers]

You could try it using the LOOKUP function as follows:

Sheet4

	A	B	C
1
2			223
3
4
5
6		24
7
8
9	------------	------------	------------
10

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula B6 =LOOKUP(C2,{1,128,192},{8,16,24}) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

A word of caution though - there is no error checking:

• i.e. a value of 127 will be valid and will return 8
• i.e. any value greater than or equal to 192 will return 24

 

[fredlo2008]

Give this a try

=IF(AND(A1>=1,A1<=126),8,IF(AND(A1>=128,A1<=191),16,IF(AND(A1>=192,A1<=223),24,"")))

 

[coastalcraig]

I must say that you folks are sooooo generous. This did indeed work for my purpose of quickly determining how may subnets are available on a network and how many hosts are indicated by the subnet mask. I also appreciate learning the logical structure of how you wrote the 'IF' statement.

If this spreadsheet is of any value to anyone I would be pleased to share it. Thank you very much!

 

[coastalcraig]

Hi Neil,

Thank you for offering another way of doing this. I plan on learning more about using 'Lookup' statements. I am grateful for your generosity!

 

[coastalcraig]

Your assistance was very valuable to me. I am taking a network class and this was the final touch on automating the calculation of the quantity of subnets and hosts on a network. Thank you