I need help creating a formula that will return a specific value based on two different criteria

I'm trying to create a formula that will return a specific value based on two different criteria.

What I need is to compare a clients age against a specific percent range and return a classification as the result.

for example, if a client is 42 years and has a BAI of 18%, the result of the formula would be "Healthy" as based on the criteria below.

I have a column for the clients age, and another column which calculates the clients BAI percent.

I am trying to avoid a laborious string of IF(AND(xxxxx) statements because that will get messy really quickly. I have entertained a COLLECT / INDEX cross reference, which would probably be easier, but I unsure how I could set that up.

Any ideas, suggestions, etc. would be appreciated. I am also available if someone wants to chat it out with me.

Thanks in advance Smartsheet Community!

Answers

  • Michael Wilkesen
    Michael Wilkesen ✭✭✭✭

    Hey @Paul Newcome, I do have this set up in Smartsheet. Thank you.

    MW

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is it structured exactly the same?

    How are you entering the client data? Are you entering (for example) "20 - 39", or are you entering the actual age of 34? Same question for the percentages.

  • Michael Wilkesen
    Michael Wilkesen ✭✭✭✭

    Hey Paul,

    Currently I have the reference ranges set up as below (screenshot).

    I log the Age and BAI calculations independently, as well as the clients sex [M/F] which is not shown below. What I would like to happen is some type of reference that would take these three data points and return the appropriate category of "Underweight," "Healthy," "Overweight," or "Obese."

    I am open to ANY suggestions, including changing the reference ranges set up. I am just at a loss on if/how this can be accomplished in the simplest way possible.

    Thanks,

    MW

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 06/14/23

    If you are really open to ANY suggestions... I did a thing. I'm not proud of it, but it works. This hard-coded and ugly solution would require you to add a [Sex] column to designate M/F. There is room for improvement, but at a certain point, readability became an issue.


    =IF(AND([Sex]@row = "M", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row < 8), "Underweight", IF(AND([Sex]@row = "M", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row >= 8, [BAI (Body Adiposity Index)]@row < 21), "Healthy", IF(AND([Sex]@row = "M", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row >= 21, [BAI (Body Adiposity Index)]@row < 26),   "Overweight", IF(AND([Sex]@row = "M", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row >= 26), "Obese", IF(AND([Sex]@row = "M", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row < 11), "Underweight", IF(AND([Sex]@row = "M", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row >= 11, [BAI (Body Adiposity Index)]@row < 23), "Healthy", IF(AND([Sex]@row = "M", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row >= 23, [BAI (Body Adiposity Index)]@row < 29), "Overweight", IF(AND([Sex]@row = "M", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row >= 29), "Obese", IF(AND([Sex]@row = "M", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row < 13), "Underweight", IF(AND([Sex]@row = "M", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row >= 13, [BAI (Body Adiposity Index)]@row < 25), "Healthy", IF(AND([Sex]@row = "M", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row >= 25, [BAI (Body Adiposity Index)]@row < 31), "Overweight", IF(AND([Sex]@row = "M", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row >= 31), "Obese", IF(AND([Sex]@row = "F", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row < 21), "Underweight", IF(AND([Sex]@row = "F", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row >= 21, [BAI (Body Adiposity Index)]@row < 33), "Healthy", IF(AND([Sex]@row = "F", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row >= 33, [BAI (Body Adiposity Index)]@row < 39), "Overweight", IF(AND([Sex]@row = "F", [Age]@row >= 20, [Age]@row <= 39, [BAI (Body Adiposity Index)]@row >= 39), "Obese", IF(AND([Sex]@row = "F", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row < 23), "Underweight", IF(AND([Sex]@row = "F", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row >= 23, [BAI (Body Adiposity Index)]@row < 35), "Healthy", IF(AND([Sex]@row = "F", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row >= 35, [BAI (Body Adiposity Index)]@row < 41), "Overweight", IF(AND([Sex]@row = "F", [Age]@row >= 40, [Age]@row <= 59, [BAI (Body Adiposity Index)]@row >= 41), "Obese", IF(AND([Sex]@row = "F", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row < 25), "Underweight", IF(AND([Sex]@row = "F", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row >= 25, [BAI (Body Adiposity Index)]@row < 38), "Healthy", IF(AND([Sex]@row = "F", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row >= 38, [BAI (Body Adiposity Index)]@row < 43), "Overweight", IF(AND([Sex]@row = "F", [Age]@row >= 60, [Age]@row <= 79, [BAI (Body Adiposity Index)]@row >= 43), "Obese"))))))))))))))))))))))))

  • Michael Wilkesen
    Michael Wilkesen ✭✭✭✭

    Thanks Carson, I appreciate the assistance, and like you, I had the exact same initial ugly idea, and I am HOPING our community might be able to have a simpler solution, because, phew....it's a lot!


    Michael

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!