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

 

[coastalcraig]

Thank you for taking the time to respond to my question. I like seeing the long form that you have used. It helps me better grasp the concept so hopefully I can do this for myself next time around.

 

[coastalcraig]

Hi Jeff,

I like this idea of the 'Lookup' statement. When it is applicable it sure seems like it would simplify things. I am a visual learner so I enjoyed the more graphical representation of your answer. In fact, for anyone that would utilizing the spreadsheet calculator I am making, they would be knowledgeable enough to not enter 127 or any number higher than they would need. So this does serve my purpose. My subnet/host network calculator is very simple, hopefully in the future I can increase its functionality. I'm not sure it would benefit anybody but if they had an interest in it I would be happy to share it if there is a way to do so that is appropriate to the rules of this forum.

I appreciate your kindness for responding to my question.

Thank you very much!