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
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!