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!

Find more posts tagged with

Accepted answers

Nic Larsen

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"))))

All comments

Nic Larsen

Try this:

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.