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

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    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

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    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"))))

  • Christina Reid
    Christina Reid ✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!