Return a specific value if between a range
I have a sheet in which several questions are asked, and depending on the answers then different scores are returned. There are seven questions, and I sum those in a column called [Imposed Risk Score]. From that total, I need it to report back a risk tolerance if the score falls between a range.
0-20: Conservative
21-40: Moderately Conservative
41-60: Moderate
61-80: Moderately Aggressive
81-100: Aggressive Growth
I can't seem to get my nested IF statements correct and the only examples I can find online don't show how to do this for multiple ranges. The only other examples I can find are vlookup and I want to avoid having another sheet that has the ranges and return values listed if possible.
Any help would be appreciated!
Best Answer
-
Try this:
=IF([Imposed Risk Score]@row >= 81, "Aggressive Growth", IF(AND([Imposed Risk Score]@row <= 80, [Imposed Risk Score]@row >= 61), "Moderately Aggressive", IF(AND([Imposed Risk Score]@row >= 41, [Imposed Risk Score]@row <= 60), "Moderate", IF(AND([Imposed Risk Score]@row <= 40, [Imposed Risk Score]@row >= 21), "Moderately Conservative", "Conservative"))))
Answers
-
Try this:
=IF([Imposed Risk Score]@row >= 81, "Aggressive Growth", IF(AND([Imposed Risk Score]@row <= 80, [Imposed Risk Score]@row >= 61), "Moderately Aggressive", IF(AND([Imposed Risk Score]@row >= 41, [Imposed Risk Score]@row <= 60), "Moderate", IF(AND([Imposed Risk Score]@row <= 40, [Imposed Risk Score]@row >= 21), "Moderately Conservative", "Conservative"))))
-
Thank you Nic! This worked like a charm and even though I tried a million different nested IF statements, I did not come close to this one. I appreciate it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!