IF formulas
Hi there
I'm pretty new with SmartSheet and am in trouble with a formula...
I want to have a formula for the following:
I have four competency levels; basic, proficient, advanced and expert. I want to get the unchanged indicator if targeted and actual competencies levels match. If the actual competency level is below the targeted one, then the down indicator should appear. If the actual competency level is higher than the targeted one, then the up indicator should appear.
Kind of....
If targeted competency level = current competency level, then unchanged indicator
If targeted competency level = "Expert" and actual competency level IS NOT "Expert", then down indicator
If targeted competency level = "Advanced" and actual competency level = "Basic" or "Proficient", then down indicator
If targeted competency level = "Advanced" and actual competency level = "Expert", then up indicator
If targeted competency level = "Proficient" and actual competency level = "Basic", then down indicator
If targeted competency level = "Proficient" and actual competency level = "Advanced" or "Expert", then green button
If targeted competency level = "Basic" and current competency level IS NOT "Basic", then up indicator
Thanks a lot for helping me! 🤔
Best Answer

Try this:
=IF([Targeted Competency]@row = [Actual Competency]@row, "Sideways", IF([Targeted Competency]@row = "Expert", IF([Actual Competency]@row <> [Targeted Competency]@row, "Down", "Sideways"), IF([Targeted Competency]@row = "Advanced", IF([Actual Competency]@row = "Expert", "Up", "Down"), IF([Targeted Competency]@row = "Proficient", IF([Actual Competency]@row = "Basic", "Down", "Up"), IF([Targeted Competency]@row = "Basic", "Up")))))
Hope this helps!
Answers

Try this:
=IF([Targeted Competency]@row = [Actual Competency]@row, "Sideways", IF([Targeted Competency]@row = "Expert", IF([Actual Competency]@row <> [Targeted Competency]@row, "Down", "Sideways"), IF([Targeted Competency]@row = "Advanced", IF([Actual Competency]@row = "Expert", "Up", "Down"), IF([Targeted Competency]@row = "Proficient", IF([Actual Competency]@row = "Basic", "Down", "Up"), IF([Targeted Competency]@row = "Basic", "Up")))))
Hope this helps!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.7K Get Help
 406 Global Discussions
 217 Industry Talk
 456 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!