Formula to Identify difference in dropdown columns without numbers

Hello!

I am working on a sheet that has three columns where two are drop downs (Current and Desired) with the following criteria:

Enthusiastic Support

Support

Neutral

Reluctant

Obstruction

Unknown

I have for the third column labeled

Gaps. In this column I am trying to find a formula that calculates the difference between Desired and Current (Desired - Current). The problem I am running into is for the formula to assign a value to the criteria so the result is a number. So from the criteria mentioned earlier the values would go 6,5,4,3,2,1,0 in the same order. I have tried using VLOOKUP and INDEX/MATCH formulas but kept getting INVALID Ref.

Find more posts tagged with

Formulas

Accepted answers

Carson Penticuff

You have values listed from 6 - 0, but only include 5 options. I wasn't sure if you were missing and option, or if it should only go to 5. This will give you results from 0-5.

=IF(AND([Current]@row <> "", [Desired]@row <> ""), IF([Current]@row = "Enthusiastic Support", 5, IF([Current]@row = "Support", 4, IF([Current]@row = "Neutral", 3, IF([Current]@row = "Reluctant", 2, IF([Current]@row = "Obstruction", 1, IF([Current]@row = "Unknown", 0)))))) - IF([Desired]@row = "Enthusiastic Support", 5, IF([Desired]@row = "Support", 4, IF([Desired]@row = "Neutral", 3, IF([Desired]@row = "Reluctant", 2, IF([Desired]@row = "Obstruction", 1, IF([Desired]@row = "Unknown", 0)))))))

All comments

Carson Penticuff

You have values listed from 6 - 0, but only include 5 options. I wasn't sure if you were missing and option, or if it should only go to 5. This will give you results from 0-5.

Travis23997

@Carson Penticuff thank you this worked!

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.