Multiple IF statements

[zardoz199]

I have a spreadsheet cell (F6) with the value BodyMassIndex (BMI) and I want to create a function that translates the BMI value to Overweight, Normal, etc.

I’ve tried to use this function but it is only returning a False value:
IF(F6>40, ,“Morbidly Obese”) =IF(AND(F6>35,F6<39.9), “Severely Obese”) =IF(AND(F6>30,F6<34.9), “Moderately Obese”) =IF(AND(F6>25,F6<29.9), “Overweight”) =IF(AND(F6>18.5,F6<24.9), “Normal”) =IF(AND(F6>16, F6<18.4), “Underweight”) =IF(AND(F6<16.1), “Severly Underweight”)

Not sure what I’m doing wrong. Help would be appreciated.

Thanks

[zardoz199]

Thank you so much JohnSUN. Just what I needed.

[JohnSUN]

@zardoz199

what I’m doing wrong

You have chooses the wrong function for your task. The If() function works well when you need to check a value and get only two results - “Yes”/“No”, “True”/“False”, “Pass”/“Fail”, “Win”/“Loss”. For cases where there are more results, this function can also be used by nesting one If() in another:

=IF(F6<16;"Severly Underweight";IF(F6<18.5;"Underweight";IF(F6<25;"Normal";IF(F6<30;"Overweight";IF(F6<35;"Moderately Obese";IF(F6<40;"Severly Obese";"Morbidly Obese"))))))

As you can see for yourself, the formula in this case turns out to be very long, it is difficult to read, it is difficult to understand, it is very difficult to change.

Since choosing a value from a table (and translating BMI into understandable text is a choice from a table) is a very common task, special functions LOOKUP(), VLOOKUP(), HLOOKUP() and others are included in programs for working with spreadsheets. For example, for your task you can use LOOKUP():
=LOOKUP(F6;{0|16|18.5|25|30|35|40};{"Severly Underweight"|"Underweight"|"Normal"|"Overweight"|"Moderately Obese"|"Severely Obese"|"Morbidly Obese"})

[eeigor]

@JohnSUN

As you can see for yourself, the formula in this case turns out to be…

IFS()
This is instead of nested IF functions, but not instead of a solution.

[JohnSUN]

@eeigor

but not instead of a solution

Why not?

=IFS(F6<16;"Severly Underweight";F6<18.5;"Underweight";F6<25;"Normal";F6<30;"Overweight";F6<35;"Moderately Obese";F6<40;"Severely Obese";F6>=40;"Morbidly Obese")

By repeating F6< many times, the formula is longer, but it will work.