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.
Best Answer
-
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)))))))
Answers
-
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)))))))
-
@Carson Penticuff thank you this worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!