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
-
Do you have the above table set up in Smartsheet?
-
Hey @Paul Newcome, I do have this set up in Smartsheet. Thank you.
MW
-
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.
-
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
-
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"))))))))))))))))))))))))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!