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.
020: Conservative
2140: Moderately Conservative
4160: Moderate
6180: Moderately Aggressive
81100: 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
Check out the Formula Handbook template!