Nested If Statement

[trevor4370]

I need a result from the following

18.5 or less = Underweight
18.5 to 24.99 = Normal Weight
25 to 29.99 = Overweight
30 to 34.99 = Obesity (Class 1)
35 to 39.99 = Obesity (Class 2)
40 or greater = Morbid Obesity

I tried this but something is wrong (O10 is the reference field)

=IF((O10>=18.4,O10<18.5),"Underweight",
IF(AND(O10>=18.6,O10<24.99),"Normal Weight",
IF(AND(O10>=25,O10<29.99,"Overweight",
IF(AND(O10>=30,O10<34.99,"Obesity (Class 1)",
IF(AND(O10>=35,O10<39.99,"Obesity (Class 2)",
IF(AND(O10>=40,"Obese", No Result")))))

 

[jtakw]

Hi,

Use LOOKUP rather than a nested IF.

"18.5 or less = Underweight
18.5 to 24.99 = Normal Weight"

I've assumed less than 18.5 = Underweight, 18.5 to 24.99 = Normal Weight

If the Tier levels may change frequently, I recommend setting up a Table for the LOOKUP formula to reference to, this is "hard-coded":

Book1
	O	P	Q
10	18.5	Normal Weight	Normal Weight
Sheet171

Cell Formulas
Range	Formula
P10	=LOOKUP(O10,{0,1,18.5,25,30,35,40},{"No Result","Underweight","Normal Weight","Overweight","Obesity (Class 1)","Obesity (Class 2)","Morbid Obesity"})
Q10	=IF(O10>=1,IF(O10<18.5,"Underweight",IF(O10<25,"Normal Weight",IF(O10<30,"Overweight",IF(O10<35,"Obesity (Class 1)",IF(O10<40,"Obescity (Class 2)","Morbid Obesity"))))),"No Result")

EDIT: I've included "No Result" in case of O10 being Blank or zero. Also included what a Nested IF would look like.

 

[lnagel]

I would think something like the following will work - It is an escalating scale where it is not possible for any O10 Value to match more than 1 of the conditions :

Dim class as string

IF O10 => 18.4 then class = "Underweight"
IF O10 => 18.6 then class = "Normal Weight"
IF O10 => 25.0 then class = "Obesity Class 1"
IF O10 => 30.0 then class = "Obesity Class 2"
IF O10 => 40.0 then class = "Obese"

 

[trevor4370]

Hi,

Use LOOKUP rather than a nested IF.

"18.5 or less = Underweight
18.5 to 24.99 = Normal Weight"

I've assumed less than 18.5 = Underweight, 18.5 to 24.99 = Normal Weight

If the Tier levels may change frequently, I recommend setting up a Table for the LOOKUP formula to reference to, this is "hard-coded":

	O	P	Q
Normal Weight	Normal Weight

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]
[TD="align: right"]18.5[/TD]

</tbody>

Sheet171​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P10[/TH]
[TD="align: left"]=LOOKUP(O10,{0,1,18.5,25,30,35,40},{"No Result","Underweight","Normal Weight","Overweight","Obesity (Class 1)","Obesity (Class 2)","Morbid Obesity"})[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q10[/TH]
[TD="align: left"]=IF(O10>=1,IF(O10<18.5,"Underweight",IF(O10<25,"Normal Weight",IF(O10<30,"Overweight",IF(O10<35,"Obesity (Class 1)",IF(O10<40,"Obescity (Class 2)","Morbid Obesity"))))),"No Result")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



EDIT: I've included "No Result" in case of O10 being Blank or zero. Also included what a Nested IF would look like.

Thank you for you help - all good now.

 

[jtakw]

You're welcome, welcome to the forum.