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

Michael Wilkesen

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!

Paul Newcome

Do you have the above table set up in Smartsheet?

Michael Wilkesen

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

MW

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

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

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

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