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.


Tags:

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    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)))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!