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

coastalcraig

New Member
Joined
Jul 11, 2012
Messages
6
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
=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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Active Member
Joined
Oct 31, 2011
Messages
403

ADVERTISEMENT

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")))
 
Last edited:

Jeff Meyers

Active Member
Joined
Mar 14, 2012
Messages
405
You could try it using the LOOKUP function as follows:

Sheet4
ABC
1
2223
3
4
5
624
7
8
9------------------------------------
10

<thead>
</thead><tbody>
</tbody>
Excel 2010

Worksheet Formulas
CellFormula
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

Active Member
Joined
Jan 12, 2012
Messages
254

ADVERTISEMENT

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

New Member
Joined
Jul 11, 2012
Messages
6
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

New Member
Joined
Jul 11, 2012
Messages
6
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

New Member
Joined
Jul 11, 2012
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,530
Members
414,245
Latest member
Major Aly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top